Ranking Based Background Color not Working?

[itsmebvk]

Hi Folks,

We have a requirement to highlight top three ranking cells based on based on sales value ranking, it should update based on slicers selections too. Example : If i select different state or city top three ranking cells should highlight based on selection.

Issue 1: This is partially working with one dimension . When I add more slicers this solution is not working (even if i add them to virtual table). I am unable to reproduce the issue here with sample data.

Issue2 : Is there any alternate way of creating this ranking measure without using virtual table. I cannot create virtual table for each requirement as we are using live connection to dataset different users have different ranking requirement. If we start creating virtual tables in datasets endup we will be creating more and more virtual tables.

Please find the attached sample data and PBI file and please provide any work around or inputs you have around this requirement.

https://drive.google.com/drive/folders/1v_NU9BwtX2wHTQF7x_2Mj4kO1Tf0bDpo?usp=sharing

Thank you.

[amitchandak]

@itsmebvk , Rank in power Bi depends on what has been used. Like if used Part and you add any other column in row/column that part would be ranked inside that

Rankx(allselected(Table[Part]), [measure],,desc,dense)

when needing more columns you need to rank like

Rankx(Summarize(allselected(Table), Table[Part], Table[Company]) , [measure],,desc,dense)

Or switch between Ranks, Using isinscope

https://docs.microsoft.com/en-us/dax/isinscope-function-dax

[itsmebvk]

@amitchandak  

Thanks for quick reply.

can we use following expressions when we multiple tables columns inthe matrix and also we have many slicer from different tables?

Rankx(Summarize(allselected(Table), Table[Part], Table[Company]) , [measure],,desc,dense)

if not is there any alternative way to avoid virtual table and create a ranking using Dax?

[amitchandak]

@itsmebvk , In summarize you can use

Fact and then dimension

Summarize(allselected(Fact), Dim[Dim1], Dim[Dim2])