Forum Discussion
Rank by measure in Direct Query
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.
OK, and what is the formula for that Rank measure? Is it just looking at available_value to rank?
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
Formula:
Rank by Product = RANKX(ALL('table1'), CALCULATE(SUM('table1'[available_value])),,DESC,Skip)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!
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 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?
