Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX: Distinct count with multiple filters

Hi, I'm quite new to DAX and I don't seem to get a good formula for the below calculation. I have many products that were sold over the years. Now, I want to distinct count the products that were ...
VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
5 years ago

Hi Anonymous 
Try this measure:

Measure = 
COUNTROWS (
    DISTINCT (
        FILTER (
            FILTER ( 'Table', YEAR ( 'Table'[Date] ) = 2021 ),
            'Table'[Sales (KG)] > 50
        )
    )
)

Table:

 

Output:

f this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your Kudos  !!

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

Anonymous Yep, VahidDM's solution was the route I was thinking about, COUNTROWS(DISTINCT(FILTER. Could also be written:

Measure =
  COUNTROWS
    DISTINCT(
      SELECTCOLUMNS(
        FILTER( 'Table', YEAR( 'Table'[Date]) = 2021 && [Sales (kg)] > 50),
        "__Product",[Product]
      )
    )
  )

 

The difference is that you are returning distinct products in the above measure versus distinct table rows. Not sure if that matters or not. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Greg_Deckler ,

     

    Thanks but it's still not working out for me. Could easily be the fact that I'm new but the tables are different.

    So I have a "Calendar" table where I want the year to be 2021.

    Than I have a measure 'Sales (kg)' that I want to be > 50

     

    Many thanks,

    Lenn