Forum Discussion
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]),'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.
12 Replies
- Create a relationship between sales and states table and then use the RANKX ( ALL (State[States] ), [Total Sales] )
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.
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 , 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/367415Thanks for your help amitchandak. How can I include the values of the STATES table into your suggested RANKX function?
Anonymous , States from Sales, I already give. If they in another table. Then if table is joined with sales use state from that table
