Rank by measure in Direct Query

[nleuck]

Hello All,

I'm trying to rank Branch Locations by a measure that finds the % of products with no sales to total inventory. When I try to create the rank measure in the visual all the locations are ranked #1. I haven't had much luck with the rank function in Power BI but maybe I'm doing something wrong.

Formula: RANKX(ALLSELECTED(table1[Branch Location], CALCULATE(DIVIDE(SUM(table2[no sales value]), SUM(table1[total inventory value])),,DESC,Skip)

I've seen other examples and it works just fine for those users, but can't figure out why it won't work for me.

Any ideas?

Thanks

[TomMartens]

Hey,

from my current understanding, adding the desc column to table matrix means adding an additional column to the current filter context.

Blocking the filter from [... id] using ALL, does not block the existing filter applied by the desc column. As the desc column values are unique there is just one row and for this rankx equals 1 for each row.

Using ALL upon the whole table, blocks the filter that are applied by the columns id and desc and of course also blocks the price column (but this is not used), and everything  works.

Hopefully, this adds some additional insights to this discussion.

Regards,

Tom 

[Greg_Deckler]

Thanks @TomMartens that makes sense and kind of what I expected was going on, that the addition of desc field was essentially making everything unique but that is a great explanation as to why.

@nleuck , I see your measure, but what is the rank measure you were using before, was it the one with ID column specified? Are there any other filters on that table visualization? It does seem odd that there are quite a few 1 rankings, I assume that is your concern?

It really is difficult to troubleshoot these things, there can be so many variables and minor or seemingly unrelated things that can cause stuff to act differently than in samples. Kudos to everyone that has contributed to this thread, it has been quite a journey thus far!

[nleuck]

@Greg_Deckler 

The RANKX measure I was using before is exactly the same except I removed the  id column from the ALL like you sugguested. I have no other filters added to the visual, nothing has been altered. I would expected it to be like this:

3154270	$101,136.94	1
3390792	$101,068.52	2
3390793	$101,068.52	2
3401548	$61,301.76	4
561405	$38,198.67	5

[Greg_Deckler]

Yeah, that is really weird, there is something that is causing RANKX to consider those values as if they were ties but that doesn't make a whole lot of sense. And it is not happening in my test data so it strikes me that there is something on your end that is causing this but for the life of me I can't figure out what that might be. If this is all in one table and these are just columns like in the test data, I don't know. 

As a stupid idea along the lines of adding blank spaces to C code and recompiling, what if you use this:

Rank by Product 2 = RANKX(ALL('Table'), CALCULATE(SUMX('Table',[price])),, DESC, Skip)

[nleuck]

It didn't change anything. This is what frustrates me the most with Power BI, when I can't seem to understand why it's working this way. It just doesn't make sense sometimes. I wish I could understand why it doesn't work in Power BI I way I expect it.

Thank you to everyone who has contributed to this post!

If you happen to figure out a possible solution I'm always willing to try them out.

[Greg_Deckler]

I don't suppose you can actually share the PBIX?

[marcorusso]

I quickly scanned the thread but I didn't understand - what is the question? 🙂

[Greg_Deckler]

Thanks @marcorusso for chiming in. It is self contained in the my last message previous to this one in the PBIX file. Basically, 

If you have this:

Rank by Product = RANKX(ALL('Table'[id]), CALCULATE(SUM('Table'[price])),, DESC, Skip)

And you put ID and Rank by Product in a table, all is well. If you then add Desc to the table, all the ranks become 1.

But this fixes it:

Rank by Product 1 = RANKX(ALL('Table'), CALCULATE(SUM('Table'[price])),, DESC, Skip)

[marcorusso]

Tom's reply is right.

A measure is evaluated in the filter context.

Every cell of a report has a different filter context.

If you have a table that has just the ID, then the initial filter context has Table[id] only.

When you have ID and DESC, then the initial filter context has Table[id] and Table[desc].

RANKX is an iterator. The first argument (A) is a table to iterate, the second argument (B) is an expression executed in a row context for each row of (A), and the third argument (C) is executed in the initial filter context. If the third argument is missing, the second argument is used instead.

The iterator creates a table in memory that has all the columns of (A) plus a column created executing (B) for each row of (A).
The presence of a context transition in (B) like CALCULATE or a measure reference generates a context transition. The context transition transform the filter context into an equivalent filter context that is applied to the initial filter context.

When (A) as only T[id], then the new filter is only T[id]-A.

If the report has only T[id] (call it T[id]-R), then the initial filter context is T[id]-R and T[id]-A overrides it, so the resulting filter context is T[id]-A.

If the report has T[id] and T[dec], then the initial filter context is T[id]-R + T[desc]-R, so T[id]-A only overrides T[id]-R, so the resulting filter context is T[id]-A+T[desc]-R.

When (A) as the entire table T[id], then the new filter is T[id]-A+T[desc]-A+T[...]-A  (add all the columns of T).

If the report has only T[id] (call it T[id]-R), then the initial filter context is T[id]-R and T[id]-A+T[desc]-A+T[...]-A overrides it, so the resulting filter context is T[id]-A+T[desc]-A+T[...]-A.

If the report has T[id] and T[dec], then the initial filter context is T[id]-R + T[desc]-R, but T[id]-A+T[desc]-A+T[...]-A overrides all the columns of T, so the resulting filter context is T[id]-A+T[desc]-A+T[...]-A.

Usually I use a graphical representation for this, but it's hard to reproduce in a text message 🙂

Other info in this article:

https://www.sqlbi.com/articles/use-of-rankx-in-power-bi-measures/

[nleuck]

Thanks again to everyone who has helped out on this post. Unfornately, RANKX will not work for me in this scenario. I ended up creating another dataset from SQL Server that does what I need.