Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measure for a DistinctCount by Categories

Hi Power BI Users,    I have a table similar to below and trying to create a new measure by using DAX to get the distinct count of prodid&customers. In short, we are trying to calculate how many or...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Vvelarde, this has been resolved by added in "KEEPFILTERS".

     

    DC Prod by Customers =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Order ] ),
        KEEPFILTERS(ALLEXCEPT ( Table1, Table1[ProductID ], Table1[Customers] ))
    )

     

    THANK YOU!

Vvelarde's avatar
Vvelarde
Community Champion
7 years ago

Anonymous 

 

Hi, try with this measure:

 

DC Prod by Customers =
CALCULATE (
    DISTINCTCOUNT ( Table1[Order ] ),
    ALLEXCEPT ( Table1, Table1[ProductID ], Table1[Customers] )
)

Regards

 

Victor

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks, Vvelarde . This works pretty well if we are not filtering the data. However, lets say if there is a date column and we would love to filter by date and check what is the distinctcount, what can be

    Vvelarde wrote:

    Anonymous 

     

    Hi, try with this measure:

     

    DC Prod by Customers =
CALCULATE (
    DISTINCTCOUNT ( Table1[Order ] ),
    ALLEXCEPT ( Table1, Table1[ProductID ], Table1[Customers] )
)

    Regards

     

    Victor


    done in order to sync with the filters? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Vvelarde, this has been resolved by added in "KEEPFILTERS".

       

      DC Prod by Customers =
      CALCULATE (
          DISTINCTCOUNT ( Table1[Order ] ),
          KEEPFILTERS(ALLEXCEPT ( Table1, Table1[ProductID ], Table1[Customers] ))
      )

       

      THANK YOU!