Filter table based on values from other table

[Anonymous]

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]),'Sales'[TotalSales])

This works well if I need to rank all staff, however I would like to limit the dataset to be ranked based on values provided in another table.

For example, let's assume my sales table looks like this:

 

And the following values in another table:

How can I get the following reult by adjusting the above DAX functions?

Please note that I cannot use a slicer to achieve this, because I need my unfiltered slicer for other visuals.

[amitchandak]

@Anonymous , try like

RankSalesStaff = RANKX(ALLselected('Sales'[State]),'Sales'[TotalSales])

Or

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Anonymous]

Thanks for your help @amitchandak. How can I include the values of the STATES table into your suggested RANKX function? 

[amitchandak]

@Anonymous , States from Sales, I already give. If they in another table. Then if table is joined with sales use state from that table 

[AntrikshSharma]

Create a relationship between sales and states table and then use the RANKX ( ALL (State[States] ), [Total Sales] )

[Anonymous]

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]

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]

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]

Like this?

Rank = 
IF (
    HASONEVALUE ( Products[Brand] ),
    RANKX ( ALLSELECTED ( Products[Brand] ), [Total Sales TREATAS] )
)

[Anonymous]

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

[harshnathani]

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]

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

[AntrikshSharma]

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.