cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ssharm43 Frequent Visitor
Frequent Visitor

count distinct Id from unique record

Hi,

I have a porblem where I have to count distinct ID's with certain conditions.

I have dataset like :

 

Customer IDCase NameFlag
1ayes
1byes
1cno
2ayes
2byes
3ano

 

and I want to count ID only once even if it is occuring multiple times with casename and according to flag

output should be like this

 

Customer IDCase NameFlagdistinct Count ID
1ayes1
1byes 
1cno1
2ayes1
2byes 
3ano1

 

please help me out with some dax expression or M-query

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: count distinct Id from unique record

Hi @ssharm43, and welcome aboard!

Based on my understanding of your question, I've provided a solution below for you.

Just as a heads-up for further learning, you'd be better posting this kind of question in either the Desktop or DAX forums in future, as the Developer forum is concerned with extending Power BI capabilities via software development tools and techniques. A lot more users frequent the other two forums and typically don't look in here very much. DAX posts in here can sometimes take a little while to get answered.

I have replicated your data into my workbook and called the table Data, so this will be reffered to in the measure I have written for you below.

It does not look exactly as your output table (with the blanks), because I personally think that if you filter rows out of a table or visual then this still needs to work correctly for any rows that remain.

Here's the output with a table with all columns (left), and for one where I take out the Case Name column (middle), and then finally the Flag (right):

image.png

Note that in each case, even though the Distinct Count ID measure is always 1, the total is 4 for both tables, because the measure is grouping by Customer ID and Flag each time.

The measure is as follows:

Distinct Count ID = 
    VAR _Groups = SUMMARIZE(
        Data,
        Data[Customer ID],
        Data[Flag]
    )
    RETURN
        COUNTROWS(_Groups)

Good luck!

Daniel


If my post solves your challenge, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Super User I
Super User I

Re: count distinct Id from unique record

Hi @ssharm43, and welcome aboard!

Based on my understanding of your question, I've provided a solution below for you.

Just as a heads-up for further learning, you'd be better posting this kind of question in either the Desktop or DAX forums in future, as the Developer forum is concerned with extending Power BI capabilities via software development tools and techniques. A lot more users frequent the other two forums and typically don't look in here very much. DAX posts in here can sometimes take a little while to get answered.

I have replicated your data into my workbook and called the table Data, so this will be reffered to in the measure I have written for you below.

It does not look exactly as your output table (with the blanks), because I personally think that if you filter rows out of a table or visual then this still needs to work correctly for any rows that remain.

Here's the output with a table with all columns (left), and for one where I take out the Case Name column (middle), and then finally the Flag (right):

image.png

Note that in each case, even though the Distinct Count ID measure is always 1, the total is 4 for both tables, because the measure is grouping by Customer ID and Flag each time.

The measure is as follows:

Distinct Count ID = 
    VAR _Groups = SUMMARIZE(
        Data,
        Data[Customer ID],
        Data[Flag]
    )
    RETURN
        COUNTROWS(_Groups)

Good luck!

Daniel


If my post solves your challenge, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

ssharm43 Frequent Visitor
Frequent Visitor

Re: count distinct Id from unique record

Thank you very much, It worked for me.
As I am posting for the first time so I was not aware of the platform. I will post on appropiate platform from the next time.

 

Thank you again.

Super User I
Super User I

Re: count distinct Id from unique record

Hi @ssharm43 - you're very welcome, and I'm glad you got what you needed!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ssharm43 Frequent Visitor
Frequent Visitor

Re: count distinct Id from unique record

Hi again,

I am sorry, I am posting here again, As you have my answer in thread, I got another situation and please help me out,

 

As I got distinct count for Yes or No, while I apply filter.

image.png

But  I want to count overall distinct Id's if "Yes" occour for any "Id" atleast once.

and I want like this without any filter as there is 

        
 Customer IDCase NameFlag  Customer IdDistinct Count
 1ayes  11
 1byes  21
 1cno  30
 2ayes  41
 2byes  total4
 3ano    
 3bno    
 3cno    
 4ayes    
 4bno    
 4cno    

 

Thank you

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Kudoed Authors