topic Re: Distinct Count If Rows are duplicate in certains conditions in DAX Commands and Tips

Distinct Count If Rows are duplicate in certains conditions

felipevaz — Mon, 09 Mar 2020 22:00:01 GMT

Hi folks,

Please, I have a table like this

Category1	Client
A	1
B	1
B	2
A	3
A	4
B	4

Distinct Count of Client

Distinct Count of Client only if Category1 has different values

2 (because Client 1 ans 4 has A and B values)

Using DAX, how can I achieve this??

Re: Distinct Count If Rows are duplicate in certains conditions

OwenAuger — Tue, 10 Mar 2020 02:42:44 GMT

Hi felipevaz

Something like this should do what you want:

Distinct Count of Client only if Category1 has different values = COUNTROWS ( FILTER ( VALUES ( YourTable[Client] ), CALCULATE ( DISTINCTCOUNT( YourTable[Category1] ) ) > 1 ) )

Regards,

Owen

Re: Distinct Count If Rows are duplicate in certains conditions

Anonymous — Tue, 10 Mar 2020 11:24:59 GMT

Hi felipevaz

Distinct Count of Client=DistinctCount(table[Client])

Distinct Count of Client only if Category1 has different values=Sumx(Summerize(table,table[Client],"Count",DistinctCount(table[Category1])),if([Count]>1,1,0))

Drag above measures in seperate card visuals.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Re: Distinct Count If Rows are duplicate in certains conditions

felipevaz — Tue, 10 Mar 2020 15:05:21 GMT

Thank you Wattamwar

It works well, but when I need to filter the Category1 the measure does not represent the right behavior. Like, if I select Category1 = A the measure does not distinct count the cases.

May I use booth cases in the same measure?
Distinct count Client by A cases OR
Distinct count Client by B cases OR
Distinct count Client by AB cases OR
Distinct count Client independently

Re: Distinct Count If Rows are duplicate in certains conditions

felipevaz — Tue, 10 Mar 2020 15:06:05 GMT

Thank you Owen

It works well, but when I need to filter the Category1 the measure does not represent the right behavior. Like, if I select Category1 = A the measure does not distinct count the cases.

May I use booth cases in the same measure?
Distinct count Client by A cases OR
Distinct count Client by B cases OR
Distinct count Client by AB cases OR
Distinct count Client independently

Re: Distinct Count If Rows are duplicate in certains conditions

felipevaz — Tue, 10 Mar 2020 16:03:00 GMT

Let me reintroduce the question...also include a new dimension here to better explains the context

Category1	Date	Client
A	01/01/2020	1
B	02/01/2020	1
B	01/01/2020	2
A	02/01/2020	3
A	01/01/2020	4
B	01/01/2020	4

The distinct count has to change if the dimensions are filtered, like

Case 1) Distinct count of Client = 4

Case 2) Distinct count of Client in 01/01/2020 = 3

Case 3) Distinct count of Client that has both A and B category = 2

Case 4) Distinct count of Client in 01/01/2020 that has both A and B category = 1

I need to measure the distinct count if Category1 has no filter, if the Client has A and B or if the Client has A OR B value...

Can it be done in one measure?? Using diferent dimensions?

Sorry my english, it is not thaaat good.

Re: Distinct Count If Rows are duplicate in certains conditions

Anonymous — Tue, 10 Mar 2020 16:42:15 GMT

It should have work with both filter and unfiltered conditions.

What do you mean by not showing right behaviour?
Thanks,
Pravin

Re: Distinct Count If Rows are duplicate in certains conditions

felipevaz — Tue, 10 Mar 2020 16:55:12 GMT

Pravin,

The function

values=Sumx(Summerize(table,table[Client],"Count",DistinctCount(table[Category1])),if([Count]>1,1,0))

is returning 2 if the "Client" has "A" and "B" values at the same time.

But this is not the total number of distinct Clients...is 4

Category1	Date	Client
A	01/01/2020	1
B	02/01/2020	1
B	01/01/2020	2
A	02/01/2020	3
A	01/01/2020	4
B	01/01/2020	4

The distinct count has to change if the dimensions are filtered, like

Case 1) Distinct count of Client = 4

Case 2) Distinct count of Client in 01/01/2020 = 3

Case 3) Distinct count of Client that has both A and B category = 2

Case 4) Distinct count of Client in 01/01/2020 that has both A and B category = 1

I need to measure the distinct count if Category1 has no filter, if the Client has A and B or if the Client has A OR B value...

Can it be done in one measure?? Using diferent dimensions?

Re: Distinct Count If Rows are duplicate in certains conditions

OwenAuger — Tue, 10 Mar 2020 21:06:03 GMT

felipevaz

Just reading your replies, are you wanting a measure that behaves differently depending on whether certain filters are applied? That is certainly possible using functions like ISFILTERED.

Do you want a measure that:

In certain cases returns regular distinct count
In other cases returns distinct count of clients with multiple Category1 values?

Both my and Anonymous 's measures posted earlier return option 2.

Could you clarify in which cases you want option 1 & option 2?

Regards,

Owen

Re: Distinct Count If Rows are duplicate in certains conditions

Anonymous — Wed, 11 Mar 2020 02:13:25 GMT

Where are you applying measure?
Is it in card or table itself?

And where are applying filter
Is it in slicer ?

If you are taking date column in slicer and the measures in card..value of measure will change with selected date in slicer.

I hve given two measures one is for distinctcount of clients.
Other one is distinctcount of clients have both a and b.

Both measures value will get change with filteres.

Thanks
Pravin