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

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 

      • AntrikshSharma's avatar
        AntrikshSharma
        Community Champion
        6 years ago

        I think you do not want the slicer to impact the visual, right? In that case you should use the Edit Interactions option. Select the slicer and then click the icon on visual that looks like the top view of a screw.