Distinct Counts on Filtered Table

[Anonymous]

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:

TEST UNIQUE COUNT = DISTINCTCOUNT('Test Table'[Super Bowl Ad Link])

&

TEST TOTAL UNIQUE COUNT = CALCULATE('Measures Table'[TEST UNIQUE COUNT], ALL('Test Table'))

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.

[Whitewater100]

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])

)

[Richard_100 · Accepted solution]

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 stuff

Hope that helps

Regards

Richard

[Anonymous]

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].

[Richard_100]

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

[Anonymous]

Ah sorry yes I wasn't using the filter correctly. Thanks a lot for your help!