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

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    A  different Approach.

     

     

    Create a Calculated Column in Sales Table.

     

    States in State Table = LOOKUPVALUE(States[States],States[States],'Sales'[STATE])

     

    Then use this measure

     

    TotalSales = CALCULATE(SUM('Sales'[Amount]),'Sales'[States in State Table] <> BLANK())

     

    Ranking = 
IF ('Sales'[TotalSales] <> BLANk(),

RANKX(FILTER(ALL('Sales'[STATE],'Sales'[States in State Table]),'Sales'[States in State Table] <> BLANK()),[TotalSales]))

     

     

    Regards,

    Harsh Nathani


    Appreciate with a Kudos!! (Click the Thumbs Up Button)

    Did I answer your question? Mark my post as a solution!

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your suggestion Anonymous. Is there a way to achieve this without having to create a calculated column?