Forum Discussion

UserSam123's avatar
UserSam123
Helper I
2 years ago
Solved

Distinctcount with sum filter

Hello everyone,   I need your help with the following problem. In the table below I would like to create a measure that gives me a separate count when the sum of the number column is less than 20....
  • UserSam123's avatar
    UserSam123
    2 years ago

    For your information, I succeeded in doing this:

     

    VAR __tmpTable = SUMMARIZE(Table,Table[Name] ,"__Brands",SUM(Table[Quantity]))
    RETURN CALCULATE(DISTINCTCOUNT(Table[Name]), FILTER(__tmpTable, [__Brands]<20))
123abc's avatar
123abc
Community Champion
2 years ago

To create a measure that gives you a separate count when the sum of the number column is less than 20, you can use the following DAX formula:

Measure = CALCULATE(DISTINCTCOUNT(Table[Name]), SUM(Table[Number]) < 20)

This formula uses the CALCULATE function to filter the table based on the condition that the sum of the Number column is less than 20. The DISTINCTCOUNT function then counts the number of unique values in the Name column that meet this condition.

Regarding your question about the page slicer, the measure will be dynamic depending on the choice in the slicer as long as all the data is in the same table.

I hope this helps! Let me know if you have any further questions.