topic Re: count distinct Id from unique record in Developer

count distinct Id from unique record

ssharm43 — Thu, 13 Feb 2020 23:08:46 GMT

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

Re: count distinct Id from unique record

dm-p — Thu, 13 Feb 2020 23:39:52 GMT

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 🙂

Re: count distinct Id from unique record

ssharm43 — Fri, 14 Feb 2020 00:01:44 GMT

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.

Re: count distinct Id from unique record

dm-p — Fri, 14 Feb 2020 00:21:00 GMT

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

Re: count distinct Id from unique record

ssharm43 — Fri, 14 Feb 2020 18:41:30 GMT

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