RANK of the Record in Calculated Table is Getting Reset when Applying Filter to pick 1 record.

[VbOnline]

I am facing a problem where, I want to rank all the records in a calculated tables based on certain parameters.

Once rank has been established, I want to only pick the record which I am interested in and I am interested in the Rank of that record among all the records.

Unfortunately, when I apply the filter, the rank is getting recalculated and with only 1 record, the Rank is changing to 1 than the original rank which was assigned along with all the records.

Any way to avoid this? Alternatively, is there a way to pick the record I need from the table without filter?

RankingTest =
VAR minselecteddate = "2022-04-01"
VAR maxselecteddate = "2022-06-30"
VAR MeasureCalc = CALCULATETABLE
(
SUMMARIZE(stn_list, stn_list[stn],stn_list[qtr])
,DATESBETWEEN(stn_list[depDate], minselecteddate, maxselecteddate)
,ALL (station_network[stationCode])
,all (BU[GroupName])
) // Has 260 Records

Below is my DAX Script:

var MeasureCalcAddMeasure = ADDCOLUMNS(MeasureCalc,"overallscore",[msroverallscore]) // Has 260 Records
var MeasureCaclAddRank = ADDCOLUMNS(MeasureCalcAddMeasure,"StnRank",RANKX(MeasureCalcAddMeasure,[overallscore],,DESC,Dense)) // Has 260 Records

var arpt = FILTER (MeasureCaclAddRank,[airport]="HYD") // with the filter, i am expecting 1 row with "Rank" Retained. But, the Rank is getting re-calculated and the Rank Is 1.
var selrank = SELECTCOLUMNS(arpt,"StationRank",[StnRank])
//var cntrows = COUNTROWS(arpt)

RETURN selrank

Finally, I need to original rank of the record I am interested in to be returned.

[VbOnline]

I am still stuck with this problem. Any suggestions or ideas on how to get this working? I do not want to create calculated column as the filters will be a challenge. So, need a way to figure out the measure filter and avoid re-set of rank.

Thanks,

Vikram

[VbOnline]

All, any help in this regard? 

[v-yadongf-msft]

Hi @VbOnline ,

Can you share with me some screenshots of your data after hiding sensitive information and tell me what is your expected output?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

[VbOnline]

@v-yadongf-msft 

The below is my base data of sales:

stn	Qtr	isMetro	SalesAmount
HYD	Q122	Yes	1000
SEC	Q122	No	950
MAS	Q122	Yes	400
LIS	Q122	No	750
LWD	Q122	Yes	200

Based on sales and other factors, we calculate the Score and the Rank of that stn based on the score:

stn	Qtr	Calculated Score via Measure	Rank
HYD	Q122	2.25	5
SEC	Q122	4.5	3
MAS	Q122	5	1
LIS	Q122	2.96	4
LWD	Q122	4.75	2

In the above, the Score and Rank are created using the DAX Script I posted in my original post.

This is the Rank for entire data set available to me.

In my Power BI Dashboard, I have a slicer for "Station".  What I am interested is, what is the Rank of the Station I am evaluationg. 

For example, for HYD Station, my rank is 5. I want to display this.

But, as the Slicer Selection is for HYD, the rank is getting re-evaluated to 1. This is happenning at this line and this is main culprit:

var arpt = FILTER (MeasureCaclAddRank,[stn]="HYD")

The Number of rows in the main calculated table is still 5:

var cntrows = COUNTROWS(MeasureCaclAddRank ) // This returns me 5 rows.

I just need the rank of station HYD from table MeasureCaclAddRank 

Hope this clarifies. Thanks

[v-yadongf-msft]

Hi @VbOnline ，

Please create a new column instead of a measure like this:

The rank will not be re-evaluated.

Best regards,

Yadong Fang

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

[VbOnline]

No, a calculated column does not work for me as the rank may vary based on slicer selection, so, the rank needs to be calculated on the fly which can only be possible via DAX.

Does Power BI does not provide a simple way to "filter a row from calculated table" with re-evaluating a measure?

[VbOnline]

@Greg_Deckler I am not sure I understand your suggestion. By the time I am trying to rank my data, I have the measure (which is used to rank) available on the table.

And in the below table, I have my rank also correctly calculated:

var MeasureCaclAddRank = ADDCOLUMNS(MeasureCalcAddMeasure,"StnRank",RANKX(MeasureCalcAddMeasure,[overallscore],,DESC,Dense)) // Has 260 Records

At this point, I only need to "Pick" 1 row I am interested in. But, I am not sure there is any other option apart from FILTER.  A Simple "where condition" to pick the row from the table which has rank already available would do the job.

Can you please eloborate a bit on what you mean by "calculated column" on this table? Do you mean persist the data with measure into a table and add a calculated column to that persisted table?

Thanks

[Greg_Deckler]

@VbOnline Implement your Rank as a calculated column and not a measure?

[VbOnline]

@Greg_Deckler  I am not sure I understand your suggestion. By the time I am trying to rank my data, I have the measure (which is used to rank) available on the table.

And in the below table, I have my rank also correctly calculated:

var MeasureCaclAddRank = ADDCOLUMNS(MeasureCalcAddMeasure,"StnRank",RANKX(MeasureCalcAddMeasure,[overallscore],,DESC,Dense)) // Has 260 Records

At this point, I only need to "Pick" 1 row I am interested in. But, I am not sure there is any other option apart from FILTER.  A Simple "where condition" to pick the row from the table which has rank already available would do the job.

Can you please eloborate a bit on what you mean by "calculated column" on this table? Do you mean persist the data with measure into a table and add a calculated column to that persisted table?

Thanks