RANKX with Slicers

[ToddChitt]

I'm having a little trouble with the RANKX function and Slicers. I'm uder the impression that when you select one or more members from a slicer on a report page, that the data available to visuals on the page is thereby filtered. Such does not apply if you display a measure that, by its definition, includes a larger or different set of data.

Here is what I have:

• A Sales Table, with column [Line Item Total]
• Sales table related to Item table on Item ID
• Vendor table related to Item table on Vendor ID
• A Calculated Column in the Vendor table: Vendor Ranking = RANKX( 'Vendors', SUMX(RELATEDTABLE('Sales'), [Line Item Total]))
• A Customer table related to Sales table on Customer ID

When I use a table visual, and include Vendor Name, Sum of [Line Item Total], and Vendor Ranking, with no slicer, everything looks good. Sort by Sum of Line Item Total and you see that the Vendor that had the most sales of their items is at the top and ranked 1, next guy is ranked 2 and so on. Looks good.

Now, I add a slicer to the page and select one single Customer. The Vendors, still sorted by Sum of Line Item Total are now ranked 1, 9, 5, 3, 4 etc. NOT 1, 2, 3...

I have also tried the following formulas for the RANKX function:

• RANKX(ALL('Vendors'), SUMX(RELATEDTABLE('Sales'), [Line Item Total]))
• RANKX(ALLSELECTED('Vendors'), SUMX(RELATEDTABLE('Sales'), [Line Item Total]))

Also treid variation of the SUMX, replacing it with a pre-summed measure from the Sales table

[Sum Line Item Total] = SUM[Line Item Total]

Ultimately, the filtered list of Customers available to the user will be driven by Row Level Security, but I need to get this working with a Customer slicer then I know RLS will be accurate.

What am I missing?

Thanks in advance.

[ankitpatira · Accepted solution]

@ToddChitt Check out this post. Use some of the code provided maybe last one will be useful.

[ToddChitt]

@ankitpatira:

I had come across that article before, and thought I was doing everything correctls. Obvously not, hence this post. The key phrase word character in that post that finally light the lamp for me was the colon : . Those two simple dots stacked vertically.

You see, for those of you who may not be familiar with the Excel version of DAX, the syntax for Calculated Measure was this:

Measure := <some definition>

And this is the syntax used in the post authored by Rob Collie,

Now, in Power BI, the syntax has been relaxed a little to this:

Measure = <some definition>

But, the syntax is the same for Columns:

Column =<some definition>

That colon sparked my brain to realize I needed to be creating my Ranking as a MEASURE, not a COLUMN. Same definition, different results.

Thanks Ankit, Thanks Rob.

FYI, I settled on a (measure) Ranking as follows:

Measure = RANKX ( ALLSELECTED ( 'Vendors' ), SUMX ( RELATEDTABLE ( 'Sales' ), [Sum Line Item Total] ) )