Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Distinctcount based on other columns - Measure

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_idcreatedCountryExplanation
NONAC-106-33564908317-Nov-23SWEDENUnreasonable compensation
NONAC-106-33564908317-Nov-23SWEDENUnreasonable compensation
NONAC-106-33564908317-Nov-23SWEDENUnreasonable compensation
NONAC-106-33564908317-Nov-23SWEDENUnreasonable compensation
NONAC-106-33564908317-Nov-23SWEDENNeeds further review
NONAC-106-33564908317-Nov-23SWEDENNeeds further review
NONAC-106-33564908317-Nov-23SWEDENNeeds further review
NONAC-106-33564908317-Nov-23SWEDENNeeds further review
AC-019-88133416217-Nov-23SWEDENNo risk identified
AC-019-88133416217-Nov-23SWEDENNo risk identified
AC-012-12753279717-Nov-23SWEDENNo risk identified
AC-012-12753279717-Nov-23SWEDENNo risk identified
AC-416-85648462117-Nov-23SWEDENNo risk identified
AC-416-85648462117-Nov-23SWEDENNo risk identified
Need Help

7 Replies

  • miTutorials's avatar
    miTutorials
    Icon for Super User rankSuper User
    2 years ago

    Please try the below measure!

     

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

     

     

    Regards

    Ismail

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    kindly try for yourslef with the sample data please.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
    Anonymous
    Not applicable
    2 years ago

    Also tried 

    Count (#~) = COUNTROWS(
                    DISTINCT(
                        UNION(
                            VALUES(pg_control_cases[case_id]),
                            VALUES(pg_control_cases[Explanation]))))