Keep one column (ID) distinct, count another column

[Anonymous]

Hi guys,

For some reason this seamingly simple task does not get to work.

I have a table that looks like this.

Action_ID  Group     Status   Description
  1                 A          Active          X
  1                 A          Active          Y
  2                 B          Pending       X
  2                 B          Pending       Y

I am trying to create a matrix per group and status:  
           Status        Active    Pending
Group 
   A                           1                0
   B                            0               1 

For this, I need to have keep the Action_ID distinct. How do I do this? I tried this, but it did not work:

CALCULATE(COUNT(Table; 'Table'[Status]); DISTINCT('Table'[Action_ID]))

[dax · Accepted solution]

Hi ChrisB1,

Because the some records don't eist in table, when you calculate this in Matrix, it won't show 0. You need to create a table like below

Table 2 = VALUES('Table (4)'[Status])

Create relationship like below on Status

Then use measure like below

Measure = DISTINCTCOUNT('Table (4)'[Status])+0

You will get the result like below

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Count Distinct in above will make it 1

[Nathaniel_C]

Hi @Anonymous ,

It actually looks like this as A has two Active. I just dragged the fields onto the matrix and use count.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[jdbuchanan71]

Hello @Anonymous 

Action Count = DISTINCTCOUNT ( 'Table'[Action_ID] )

Then put the group in the rows and status in the columns with this measure in the values.  Should get you what you are looking for.

[amitchandak]

Are you using the matrix table?

In matrix table, you drag group on row and status and column and action_id in value and click on arrow and change agg to count distinct