Distinctcount based on other columns - Measure

[Anonymous]

Hi Experts

I am trying to count the distinct number of values based on two other columns - Country and Explaination - but the distinct count value is wrong.. see measure and sample date

Measure

Count (#~) = CALCULATE(DISTINCTCOUNT(pg_control_cases[case_id]),ALL(pg_control_cases[Explanation])) - the result should be 5 but i am getting 4.

case_id	created	Country	Explanation
NONAC-106-335649083	17-Nov-23	SWEDEN	Unreasonable compensation
NONAC-106-335649083	17-Nov-23	SWEDEN	Unreasonable compensation
NONAC-106-335649083	17-Nov-23	SWEDEN	Unreasonable compensation
NONAC-106-335649083	17-Nov-23	SWEDEN	Unreasonable compensation
NONAC-106-335649083	17-Nov-23	SWEDEN	Needs further review
NONAC-106-335649083	17-Nov-23	SWEDEN	Needs further review
NONAC-106-335649083	17-Nov-23	SWEDEN	Needs further review
NONAC-106-335649083	17-Nov-23	SWEDEN	Needs further review
AC-019-881334162	17-Nov-23	SWEDEN	No risk identified
AC-019-881334162	17-Nov-23	SWEDEN	No risk identified
AC-012-127532797	17-Nov-23	SWEDEN	No risk identified
AC-012-127532797	17-Nov-23	SWEDEN	No risk identified
AC-416-856484621	17-Nov-23	SWEDEN	No risk identified
AC-416-856484621	17-Nov-23	SWEDEN	No risk identified

[Ahmedx · Accepted solution]

pls try

[Anonymous]

Also tried 

Count (#~) = COUNTROWS(

                DISTINCT(

                    UNION(

                        VALUES(pg_control_cases[case_id]),

                        VALUES(pg_control_cases[Explanation]))))

[Anonymous]

Why do you expect the result to be 5? There are only 4 unique values in the Case_ID column. Are you double-counting NONAC-106-335649083 because it has 2 Explanations?

[Anonymous]

Hi Helper 1 there are indeed 4 based on case id when you take into consideration explantation then one id is repeated for repeated Unreasonable compensation and Needs further review then we have 5

[Anonymous]

kindly try for yourslef with the sample data please.

[Anonymous]

Hi Ismal - not getting the expect result its just counting the number of rows.

[miTutorials]

Please try the below measure!

Measure = COUNTROWS(SUMMARIZE('Table','Table'[case_id],'Table'[Country],'Table'[Explanation]))

Regards

Ismail