Rank by measure in Direct Query

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 

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/

Greg_Deckler 

I even tried creating sample data in an Excel spreadsheet with columns id, desc, and price. I still am getting the same results. Whenever I add the desc column it decide to rank everything as 1.

That's great! Can you share the Excel file via OneDrive or box or something? 

Greg_Deckler 

Can you copy the data from this? I named the first column id (whole number field), desc (text field), price (currency field).

Yep! That's great, let me take a look.

Oh man, so much easier. Odd though. Simple fix, remove the column reference, not sure why the heck that was causing an issue exactly:

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

 Attached the PBIX. marcorusso would be eternally greatful if you could impart your wisdom as to why RANKX function is acting in this manner with a column reference to the ID field versus the full table. 

Greg_Deckler 

Okay it worked it the sample as expected, but I'm still having an issue with my original dataset. When I add the description colum to the visual nothing changes which I would expect. The only issue is now the ranking doesn't seem right. It should be exactly the same as the sample data I created. See below.

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:

OK, and what is the formula for that Rank measure? Is it just looking at available_value to rank?

Greg_Deckler 

Formula: 

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!

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:

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)

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.

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

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.