Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Help with Distinct Count

Hi, 

 

I am trying to write some DAX to get the distict count of SKUs that have a certain threshold of CM%. I have a measure calculating CM % and a measure calculating the distinctcount of SKUs in the full table. The formula works if I use rowcount but when I used distinctcount, the formula doesnt work...

 

CountofSKUsnegCM% = CALCULATE(DISTINCTCOUNT(Data[SKU]),[CM%]<0) gets an error of A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
 
Any idea how to solve this?
Need Help

2 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    3 years ago

    Hi Anonymous 
    Modify your formula by adding the "filter" function.
    Something like :
    CountofSKUsnegCM% = CALCULATE(DISTINCTCOUNT(Data[SKU]), filter (your table,[CM%]<0))In case it still doesn't work out,

    please attach your table and the desired result.

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi!

       

      Thanks for the help! It accepts the formula (no error) but when i drop it into a card it is showing as blank. Not sure why that would be... heres the updated formula:

       

      CountofSKUsnegCM% = CALCULATE(DISTINCTCOUNT(Data[SKU]),FILTER(Data,[CM%]<0))
       
      Here is the table that the number should reflect with the associated filters applied (CM<0)
       

       

      The count measure above is:

       

      CountofSKUs = DISTINCTCOUNT(Data[SKU])
       
      Let me know if you need anything else!