Forum Discussion
Helper IVDynamic Rank 1st, 2nd and 3rd Filtering Based on Slicer Selection
Super UserPowerBI-Newbie , Use index function. You can use Parameter and measure to make it dynamic
example
Top1 =
calculate([%scrap], KEEPFILTERS(index(1, All(SAPWorkCenter[CDL]), orderby([%scrap],desc) ) ))
Top 2=
calculate([%scrap], KEEPFILTERS(index(2, All(SAPWorkCenter[CDL]), orderby([%scrap],desc) ) ))
Top N=
calculate([%scrap], KEEPFILTERS(index([selected Parameter], All(SAPWorkCenter[CDL]), orderby([%scrap],desc) ) ))
Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U
Helper IVHi amitchandak ,
Thank you very much for your response. It's gotten me half-way there after adapting it for my need.
I have the following measure:
2nd Highest Location =
VAR _2ndValue = INDEX(2,SUMMARIZE(ALLSELECTED(Issues[Location]),Issues[Location], "_count not cancelled",[Count Not Cancelled]),ORDERBY([Count Not Cancelled],DESC))
RETURN
MAXX(_2ndValue,[Location])
This provides me with the name of the location with the 2nd highest value which is excellent and so I wanted to use it in the following measure for the y-axis so that the categories and sub-categories can be filtered based on 2nd highest location:
Count - Not Cancelled 2nd =
CALCULATE(COUNT(Issues[Incident Date]),
Issues[Count If not cancelled] = 1,
Issues[Location] = [2nd Highest Location])+0
But this gives me PLACEHOLDER error and so I tried using variables but it doesn't filter the data properly. However, when I hardcode the value like the following it works perfectly:
Issues[Location] = "Location 2"
I don't know what the issue is.
- PowerBI-Newbie
Hi amitchandak,
Any assistance is greatly appreciated.
- PowerBI-Newbie
Hi amitchandak,
I hope you're well.
I don't want to create a new question so would greatly appreciate your help with the above query.
- amitchandak
PowerBI-Newbie , if this from a connected table, measure can be filtered using a code like
calculate([%scrap], KEEPFILTERS(index(2, All(SAPWorkCenter[CDL]), orderby([%scrap],desc) ) ))
but if you first want to get value and pass it on to another table
calculate([Meausre], filter(Table, Table [Location] = [2nd Highest location]))
if you will use +0, it will show all other location with blank values
- PowerBI-Newbie
Hi amitchandak,
Where do I use +0? And where do I put this measure?