A quick post today about how to use RANKX based on slicer selection in Power BI.

A RANKX return the ranking of a number for each row in a table. The following DAX rank the Sales Amount for each Product and works fine without a slicer.

= RANKX(ALL(Products), SUMX(RELATEDTABLE(InternetSales), [SalesAmount]))

The solution below will show how to use RANKX with a slicer and get the expected result. I have two tables: Employees and Orders, related by Employee ID.

I have the following table with RANKX that works fine without a slicer but does not work as expected if you are using a slicer as seeing in second table below for (RANK1) Measure.

RANK1 = RANKX(ALL(Employees), SUMX(RELATEDTABLE(‘Order’), [TotalUnitCost]))

With below measure (RANK2), when you select a slicer for each Country, you will see the expected result independent of another Country.

RANK2 = CALCULATE( RANKX(ALL(Employees[FirstName]),[TotalUnitCost], ,DESC))

Note: TotalUnitCost = SUM(‘Order'[UnitPrice])

Happy data modelling 🙂