Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ssharm43
Helper I
Helper I

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
dm-p
Super User
Super User

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!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




View solution in original post

4 REPLIES 4
ssharm43
Helper I
Helper I

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.

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!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




dm-p
Super User
Super User

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!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors