Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filter table based on values from other table

I am using this DAX meassure to calculate total sales   TotalSales = SUM(Sales[Amount])     and this ranking formula to rank the sales staff:   RankSalesStaff = RANKX(ALL('Sales'[Staff_ID])...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you for the suggestion AntrikshSharma. However, due to the setup up my data model I cannot create a relationship between the two tables and have to use DAX to solve the issue.

AntrikshSharma's avatar
AntrikshSharma
Community Champion
6 years ago

Then you will have to use TREATAS for example in my model I broke the relationship between products and sales but using TREATAS i can create a virtual relationship:

Total Sales TREATAS =
CALCULATE (
    [Total Sales],
    TREATAS ( VALUES ( Products[ProductKey] ), Sales[ProductKey] )
)
Rank =
IF (
    HASONEVALUE ( Products[Brand] ),
    RANKX ( ALL ( Products[Brand] ), [Total Sales TREATAS] )
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks AntrikshSharma. Based on your description, this is exactly what I am after. However, I am struggling to get it to work, because my equivalent of your Products table is filtered by a slicer and does only return one value right now. Any suggestion on how I can fix that?

    • AntrikshSharma's avatar
      AntrikshSharma
      Community Champion
      6 years ago

      Like this?

      Rank = 
IF (
    HASONEVALUE ( Products[Brand] ),
    RANKX ( ALLSELECTED ( Products[Brand] ), [Total Sales TREATAS] )
)
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        No, all product brands should be included regardless of what is selected on the slicer. Thanks so much for your patience AntrikshSharma