cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 88 members 1,292 guests
Please welcome our newest community members: