Forum Discussion
Distinct Counts on Filtered Table
I'm having some trouble getting the intended distinct count numbers on a filtered table. From the results I'm seeing it appears as though the filtered table isn't actually filtered, so I'm not sure if that's the problem or if I'm using the wrong DAX.
The link to OneDrive containing the PBIX file is here.
GOAL
I have the following two DAX measures:
I have a slicer on a table upstream of 'Test Table' called 'Category Table'[Category], and when 'Category Table'[Category] = 'Animals', I'm expecting to see the following splits by 'Test Table'[Brand]:
| Brand | TEST UNIQUE COUNT | TEST TOTAL UNIQUE COUNT |
| Bud Light | 4 | 10 |
| Budweiser | 5 | 9 |
| Coca-Cola | 2 | 6 |
| Doritos | 4 | 9 |
| E-Trade | 0 | 2 |
| Hyundai | 3 | 7 |
| Kia | 3 | 7 |
| NFL | 0 | 6 |
| Pepsi | 1 | 6 |
| Toyota | 1 | 7 |
PROBLEM
I'm actually seeing the following table:
| Brand | TEST UNIQUE COUNT | TEST TOTAL UNIQUE COUNT |
| Bud Light | 10 | 69 |
| Budweiser | 9 | 69 |
| Coca-Cola | 6 | 69 |
| Doritos | 9 | 69 |
| E-Trade | 2 | 69 |
| Hyundai | 7 | 69 |
| Kia | 7 | 69 |
| NFL | 6 | 69 |
| Pepsi | 6 | 69 |
| Toyota | 7 | 69 |
I think there may be a problem with the data model, in the screenshot below, it seems like the the 'Category Table' isn't filtering the 'Test Table' for some reason. Also I can't connect 'Category Table' to the 'Superbowl Data' as that will mess up all my visuals.
The problem seems fairly trivial, so it may have been posted about before. I couldn't find any other posts regarding this when I searched, so apologies if it has already been covered.
Any help would be appreciated.
Hello
I was able to show your required data using this measure:
TEST TOTAL UNIQUE COUNT 2 =CALCULATE(DISTINCTCOUNT('Test Table'[Super Bowl Ad Link]),ALL('Category Table'[Category]))Putting the ALL against the 'Category Table' rather than the 'Test Table' removes the "= Animals" filter over the table, making the DISTINCTCOUNT work. I also used the DISTINCTCOUNT formula directly within the CALCULATE instead of referencing the [TEST UNIQUE COUNT] measure to avoid the hidden nested CALCULATE stuffHope that helps
Regards
Richard
5 Replies
Hello
I was able to show your required data using this measure:
TEST TOTAL UNIQUE COUNT 2 =CALCULATE(DISTINCTCOUNT('Test Table'[Super Bowl Ad Link]),ALL('Category Table'[Category]))Putting the ALL against the 'Category Table' rather than the 'Test Table' removes the "= Animals" filter over the table, making the DISTINCTCOUNT work. I also used the DISTINCTCOUNT formula directly within the CALCULATE instead of referencing the [TEST UNIQUE COUNT] measure to avoid the hidden nested CALCULATE stuffHope that helps
Regards
Richard
Hi Richard,
Thanks for your answer, your formula for TEST TOTAL UNIQUE COUNT 2 has worked. I'm just wondering how you got the TEST UNIQUE COUNT function to work? Screenshot below.
TEST UNIQUE COUNT = DISTINCTCOUNT('Test Table'[Super Bowl Ad Link])
For some reason the TEST UNIQUE COUNT is ignoring the filter on 'Category Table'[Category].
Hello
I left your original DAX for the TEST UNIQUE COUNT untouched, this is a visual with your two original measures plus my new total.
I use the 'Category Table'[Category] field for the filter, not the 'Test Table'[Category] field. Your original TEST UNIQUE COUNT is showing the 4, 5, 2, 4, etc numbers as below in my view
Orig TEST UNIQUE COUNT =DISTINCTCOUNT('Test Table'[Super Bowl Ad Link])I can see you've said that it's ignoring the 'Category Table'[Category] filter but if you could just double check and then maybe share your workbook again? (By the way, sharing the pbix file was very helpful)
Hope that helps anyway
Regards
Richard
Hi:
I think this solution maybe be similiar. Is this correct for you? You want to not use Table Descriptions when you have a measure in the formula..Thanks
TEST UNIQUE COUNT =DISTINCTCOUNT('Test Table'[Super Bowl Ad Link])New Measure II = CALCULATE([TEST UNIQUE COUNT],REMOVEFILTERS('Category Table'[Category]))New Measure = CALCULATE(DISTINCTCOUNT('Test Table'[Category]))
