Calculate distinct count based on 2 columns

[Anonymous]

Hi 

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')))

 

[Anonymous · Accepted solution]

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

[Anonymous]

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

[shravanreddy133]

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])
)
)

[Anonymous]

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.