Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 i...
  • Richard_100's avatar
    Richard_100
    4 years ago

    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


     

Richard_100's avatar
Richard_100
Resolver I
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Richard_100
      Resolver I
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

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