RANKX - Unable to RANK Rows For Each Category

[WinterMist]

Hello Community - 

Frustrated with myself that I can't figure this out.

I've read about 10-15 related pages on forums, SQLBI etc., but still failing.

Seems like this should be incredibly easy.

All I want to do is rank the rows for each City within a table visual based on Data[Unique ID].

NOTES:

- There can be no ties.

- Dummy data set is shown below.

- Since there is no unique column to break ties, I created the calculated column "Unique ID".

Desired outcome is a table visual that shows the following Excel mock-up data:

Regards,

Nathan

[WinterMist]

@v-yueyunzh-msft 

Hello Aniya,

Thanks again so much for your detailed responses.

Very helpful.

Regards,

Nathan

[Take3]

@v-yueyunzh-msft 

Hello Aniya,

1) Thank you very much!  Could not have solved this without you.  This definitely works & I will mark it as a solution shortly.  I just want to understand a bit better.

2) In general, is there a way to see the results of DAX tables within the current Filter Context (FC).

For example, parameter 1 (P1) is highlighted here below:

I understand in theory that this CALCULATETABLE creates a single column table of Unique ID values, 

removing ALL filters EXCEPT the filter on City.

However, what if I want to actually SEE the result set of this CALCULATETABLE by itself?

How can I do that?

- I cannot display it in the report (in its own separate measure) because it's a TABLE and not a SCALAR value.

- I cannot truly see the results in DAX Studio either, because I lose the FC of the table visual.

As you can see below, both of the following EVALUATE statements in DAX Studio return identical results;

both the query with the ALLEXCEPT clause & the query without it.

Is there a way to actually see the result set of this CALCULATETABLE?

If this is not possible, could you at least explain what the actual results of this CALCULATETABLE look like within the current FC?

3) I created a second measure, based off the one you gave me; the only difference being that I separated each parameter into its own variable.  But I noticed that this does not work.

- Is this because my code does not use CT (Context Transition), whereas yours does?  I believe CT occurs due to CALCULATE being inside of an iterator (e.g. RANKX).

Thanks again so much for your help!

Nathan

[v-yueyunzh-msft]

Hi, @Take3 

Thanks for marking my solution first! You are so welcome!~ For your question, this is my understand about them:

(1)We cannot receive the impact of filtering context (FC) when performing corresponding Table operations in DAX STUDIO, etc., that is, we cannot view the tables generated under a certain FC. We can only simulate the tables and data that may be generated in the current filtering context (FC) through imagination.

Your understanding is very correct, CALCULATETABLE creates a single column table of Unique ID values, removing ALL filters EXCEPT the filter on City .

In fact, the CALCULATETABLE() function is just a function that modifies the current filtering context, similar to the CALCULATE() function, rather than modifying the filtering context of the table function.

For example, when I am in [City]="Los", I will return {10147,10209,10372,10384,10432}；When I am in [City]="New York", I will return {10707,10716,10721,10727,10778}.

(2)For your second question, when you use the var in the dax code , it will not work . This is due to the "CALCULATE( SUM('Table'[Unique ID]),ALLEXCEPT('Table','Table'[Unique ID]))" can not be defined out of the rankx() function.

Because our variables are calculated during use, if defined as variables, they will be evaluated in the outer layer.

Our RANKX() is a row context that performs line by line calculations. We need to include the calculated formula in this formula in order for our row context to calculate the corresponding correct result.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft · Accepted solution]

Hi , @WinterMist 

Thanks for your sample data and end result first!
According to your description, you want to rank by the [Unique ID] group by the [City].

Here are the steps you can refer to :
(1)If you want to create a calculated column in the table , you can use this dax:

Column = RANKX( FILTER('Table','Table'[City] = EARLIER('Table'[City])) , 'Table'[Unique ID] ,,ASC ,Dense)

(2)If you want to create a measure , you can use this dax:

Measure = RANKX(CALCULATETABLE( VALUES('Table'[Unique ID]), ALLEXCEPT('Table','Table'[City])) , CALCULATE( SUM('Table'[Unique ID]),ALLEXCEPT('Table','Table'[Unique ID])) ,,ASC,Dense)

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly