topic Re: Calculate distinct count based on 2 columns in DAX Commands and Tips

Calculate distinct count based on 2 columns

Anonymous — Fri, 01 Mar 2024 01:56:22 GMT

I'm trying to get the distinct count of every project assigned to each unit. For the days that there's only one unit I'm getting the calculation right but not for those days with 2 or more units.

With this formula I'm getting the following result:

count project =

SUMX(VALUES('HS per Project'[Ship Date (bins)]), CALCULATE(DISTINCTCOUNT('HS per Project'[Project]), ALLSELECTED('HS per Project')))

Re: Calculate distinct count based on 2 columns

shravanreddy133 — Fri, 01 Mar 2024 03:06:47 GMT

Try Using ALLEXCEPT instead of ALL SELECTED:

SUMX(
VALUES('HS per Project'[Ship Date (bins)]),
CALCULATE(
DISTINCTCOUNT('HS per Project'[Project]),
ALLEXCEPT('HS per Project', 'HS per Project'[Unit])
)
)

Re: Calculate distinct count based on 2 columns

Anonymous — Fri, 01 Mar 2024 16:11:50 GMT

Hi shravanreddy133 it did fix the units that does not repeat over the days but everything else is wrong. Seems that now it's counting the projects of the same unit without considering they are from different dates.

Re: Calculate distinct count based on 2 columns

Anonymous — Mon, 04 Mar 2024 05:45:37 GMT

Hi Anonymous ,
Thanks to shravanreddy133 for the great ideas.
This dax can be modified as

Count Project = SUMX( VALUES('HS per Project'[Ship Date]), CALCULATE( DISTINCTCOUNT('HS per Project'[Project]), ALLEXCEPT('HS per Project', 'HS per Project'[Ship Date], 'HS per Project'[Unit]) ) )

Fianl output

Re: Calculate distinct count based on 2 columns

Anonymous — Mon, 04 Mar 2024 20:11:17 GMT

Thanks for the help Anonymous. This one solved my problem!