Distinct Count If Rows are duplicate in certains conditions

[felipevaz]

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

4

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??

[felipevaz]

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.

[Anonymous]

It should have work with both filter and unfiltered conditions.

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

[felipevaz]

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?

[Anonymous]

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

[OwenAuger]

@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:

1. In certain cases returns regular distinct count
2. 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

[Anonymous]

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.

[felipevaz]

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

[OwenAuger]

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

[felipevaz]

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