Forum Discussion
Keep one column (ID) distinct, count another column
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:
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])+0You will get the result like below
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
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
- 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.
- Nathaniel_C
Community Champion
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 - amitchandak
Count Distinct in above will make it 1
daxCommunity Support
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])+0You will get the result like below
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
