cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrisB1 Regular Visitor
Regular Visitor

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:

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



1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Keep one column (ID) distinct, count another column

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

355.PNG

Then use measure like below

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

You will get the result like below

356.PNG

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.

View solution in original post

5 REPLIES 5
amitchandak Super Contributor
Super Contributor

Re: Keep one column (ID) distinct, count another column

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

Super User
Super User

Re: Keep one column (ID) distinct, count another column

Hello @ChrisB1 

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 Super Contributor
Super Contributor

Re: Keep one column (ID) distinct, count another column

Hi @ChrisB1 ,

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

active.PNG

amitchandak Super Contributor
Super Contributor

Re: Keep one column (ID) distinct, count another column

Count Distinct in above will make it 1

Screenshot 2019-09-11 21.57.24.png

Community Support Team
Community Support Team

Re: Keep one column (ID) distinct, count another column

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

355.PNG

Then use measure like below

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

You will get the result like below

356.PNG

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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 46 members 927 guests
Please welcome our newest community members: