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
AntrikshSharma's avatar
AntrikshSharma
Community Champion
6 years ago
Create a relationship between sales and states table and then use the RANKX ( ALL (State[States] ), [Total Sales] )
  • 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?