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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi VahidDM 

     

    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

    • VahidDM's avatar
      VahidDM
      Icon for Super User rankSuper User
      5 years ago

      Hi Anonymous 

       

      Ii it possible to share sample of your table(s) here? not as a screenshot😎

      Or can you share your PBIX file here?(Dropbod, gofile.io, google drive)

       

      Appreciate your Kudos ✌️ !!