New measure with DISTINCTCOUNT with FILTER

[yellow43]

Hello community!

I am having the following issue:

I would like to have a Card with the number of product (SKU) that have less than 100 units sold.

As you can see on table below, I have 25 SKU, but the card shows 28 SKU.

I have created a new measure with the following:

Products < 100 = CALCULATE (

    DISTINCTCOUNT ( Table[SKU] ), 

    FILTER(ALL(Table[SKU]), [Total Sales] < 100

))

Also tried:

Products <100 = COUNTROWS(
FILTER(VALUES(Table[SKU]); [Total Sales]<100))

But it seems that is not working as I expected, because it shows 28.

This numbers of sales are calculated based on measure that I have created:

Total Sales = SUM(Table[Sales_Invoice])

Thank you in advance for your help.

[v-yalanwu-msft]

Hi, @yellow43 ;

You could try it.

Products < 100 =
CALCULATE (
    DISTINCTCOUNT ( Table[Products] ),
    FILTER ( ALLSELECTED ( 'Table' ), [Total Sales] < 100 )
)

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim · Accepted solution]

Hi,

I assume the measure needs to provide count of products that show less than 100 per selected day.

If you have a Calendar Dimension Table, please try the below measure whether it works.

Products < 100 =
CALCULATE (
    DISTINCTCOUNT ( Table[SKU] ),
    FILTER ( SUMMARIZE ( Table, Table[SKU], Calendar[Date] ), [Total Sales] < 100 )
)