Distinctcount not showing total

DAX DAX Calculations
1

I have a table with a unique assignment_id and unique client_id. Both ids appear multiple times in the table. One client_id can have multiple assignment_id. I want a list of clients that only have one assignment. The following measure works correctly but the total is missing. Does anyone know how to fix this?
Enterprise DNA.pbix (428.5 KB)

New Clients =
VAR tbl =
SUMMARIZE(
‘Fact Table’,
‘Fact Table’[CDASSIGN_ID],‘Fact Table’[CLIENT_ID])

VAR idcount = DISTINCTCOUNT(‘Fact Table’[CDASSIGN_ID])
RETURN
CALCULATE( DISTINCTCOUNT( ‘Fact Table’[CLIENT_ID]), FILTER(tbl, idcount=1), FILTER(‘Fact Table’,‘Fact Table’[CDASSIGN_VOID_FLAG] <> “Y”))

image

2

Hi @RDW,
Once you have the mean, to correct its calculation in the total of the table you can create the following measure:

Total New Clients =
VAR tbl =
ADDCOLUMNS(
     VALUES( 'FactTable'[CLIENT_ID] ),
         "@NewClients", [NewClients]
)
VAR _Result = IF(
     HASONEVALUE( 'Fact Table'[CLIENT_ID] ),
     [NewClients],
     SUMX( tbl, [@NewClients] )
)

RETURN
_Result

Captura

Here is a link to @Greg’s forum post where he explains how we can correct the measure calculation in the totals.

Fix Incorrect Totals - DAX Patterns - Enterprise DNA Forum

Regards,

Enterprise DNA_JAFP.pbix (429.2 KB)

3

Thank you @jafernandezpuga this worked perfectly and the video is helpful.

1 Like