Forum Discussion
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 ID | Case Name | Flag |
| 1 | a | yes |
| 1 | b | yes |
| 1 | c | no |
| 2 | a | yes |
| 2 | b | yes |
| 3 | a | no |
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 ID | Case Name | Flag | distinct Count ID |
| 1 | a | yes | 1 |
| 1 | b | yes | |
| 1 | c | no | 1 |
| 2 | a | yes | 1 |
| 2 | b | yes | |
| 3 | a | no | 1 |
please help me out with some dax expression or M-query
Thank you
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):
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 🙂
4 Replies
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):
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 🙂
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.
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 ID Case Name Flag Customer Id Distinct Count 1 a yes 1 1 1 b yes 2 1 1 c no 3 0 2 a yes 4 1 2 b yes total 4 3 a no 3 b no 3 c no 4 a yes 4 b no 4 c no Thank you
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.
