Forum Discussion
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):
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 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