Dynamic RANK Column based on Slicer selection

[amsrivastavaa]

Hi Guys!!

I need to create RANK COLUtMN in such as way that it will dynamically change if slicer is applied, i,e,

Let say, I have table as shown below : 

Project	Year	Revenue	RANK
P-1	2017	110	6
P-2	2017	150	5
P-3	2017	250	4
P-1	2018	350	3
P-2	2018	450	2
P-3	2018	550	1

I have sclier of Project.

What I want is, when I have selected P-1 and P-3 project in the slicer, our table will look like that with chaning RANK.

Project	Year	Revenue	RANK
P-1	2017	110	4
P-3	2017	250	3
P-1	2018	350	2
P-3	2018	550	1

i.e. RANK Will be calculated based on what ever data left, as in this case, as soon as P-2 was unselected, RANK will be based on what ever the data left.

I need RANK as COLUMN of table NOT as a MEASURE which will be used in the VISUAL for filteration.

Thanks

Amit Srivastava

[Ashish_Mathur]

Hi,

You should write a measure for this and then drag that measure to the filter pane for applying conditions.  These measures work

Rev = SUM(Data[Revenue])

Rank = RANKX(GENERATE(ALLSELECTED(Data[Project]),ALLSELECTED(Data[Year])),[Rev])

Hope this helps.

[amsrivastavaa]

Hi @Ashish_Mathur ,

Thanks for the reply 

Actually, I have achieved this via Measure as you suggested however it does not suits my ultimate requirement.

Below is the detail of my ultimate requirements, please provide your thoughts on the same.

https://community.powerbi.com/t5/Desktop/Power-BI/m-p/2876038#M990104

Thanks

Amit Srivastava 

[Anonymous]

HI @amsrivastavaa,

Current power bi does not support creating dynamic calculated columns/tables based on filter effect, they do not work on the same data levels. Please create a measure expression instead.

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[amsrivastavaa]

Hi @Anonymous , Thanks for the message.

Below is the URL of my ultimate requirement, please suggest whether it is possible to implement the same..

https://community.powerbi.com/t5/Desktop/Power-BI/m-p/2876038#M990104

Thanks

Amit 

[Anonymous]

Hi @amsrivastavaa,

It seems like you want to ranking a report that design as dynamic attribute report.

Dynamic Attributes In A Power BI Report 

Current rank function seems not suitable to ranking based on multiple fields, you can try write a measure formula to use countrow with isonorafter function instead of the rankx.

Sorting by multiple columns 

ISONORAFTER function (DAX) - DAX | Microsoft Learn

Regards,

Xiaoxin Sheng

[amsrivastavaa]

Hi @Anonymous ,

I am able to manage Ranks, Thanks for your help.

Could you please help me on my ultimate requirement that has been detailed in below URL

https://community.powerbi.com/t5/Desktop/Power-BI/m-p/2876038#M990104

Thanks

Amit