Forum Discussion
Helper IDistinct Count If Rows are duplicate in certains conditions
Helper ILet 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.
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?
- OwenAuger
Super User
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
- 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
