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

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.

    • 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?