Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi PBI Community,
I am trying to do a lookup based on priority. For instance, I need to pick a rank for Table 1. I need to first check if there is a value "Rank 1" for A. If yes, pick that, else pick "Rank 2". Or may be there is another way to do this?
Any help would be appreciated.
Thank you.
Solved! Go to Solution.
Hi @Anonymous,
You can do the following:
Rank Sort
Rank6 | 1 |
Rank5 | 2 |
Rank3 | 3 |
Rank1 | 4 |
Rank2 | 5 |
Rank4 | 6 |
Rank_Sort = LOOKUPVALUE('Rank'[Sort];'Rank'[Rank];Table2[Ranking])
First_Ranking = LOOKUPVALUE ( 'Rank'[Rank]; 'Rank'[Sort]; MINX ( SUMMARIZE ( Table2; Table2[ID]; "RANK"; MIN ( Table2[Rank_Sort] ) ); [RANK] ) )
Then add the levels you need to have the value as you want.
As you can see the colours are matching with the first rank in the ranking table.
Although there is a repetion on the column rank_sort this is need to have a way of getting the order you need.
The table 1 and table 2 have a relantionship between them as I posted in my fist answers that's why I'm choosing ID from table 1 and measure is on table 2.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous,
don't know how you want to show this on your visuals but you can create a relationship between both table and then select the First value and it will return the 1st rank/ 2nd rank,
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix,
Thank you for your response.
The actual values are not really Rank1,2,3 and sorting them alphabetically would not work.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêsyes, for instance if there is a match for both Rankin1 and Ranking6, I want it to pick Ranking6.
Hi,
On what basis do you say ranking6? Does it have to pick up the rank with the highest number at the end? Show a simple example and the expected result.
Hi @Anonymous,
You can do the following:
Rank Sort
Rank6 | 1 |
Rank5 | 2 |
Rank3 | 3 |
Rank1 | 4 |
Rank2 | 5 |
Rank4 | 6 |
Rank_Sort = LOOKUPVALUE('Rank'[Sort];'Rank'[Rank];Table2[Ranking])
First_Ranking = LOOKUPVALUE ( 'Rank'[Rank]; 'Rank'[Sort]; MINX ( SUMMARIZE ( Table2; Table2[ID]; "RANK"; MIN ( Table2[Rank_Sort] ) ); [RANK] ) )
Then add the levels you need to have the value as you want.
As you can see the colours are matching with the first rank in the ranking table.
Although there is a repetion on the column rank_sort this is need to have a way of getting the order you need.
The table 1 and table 2 have a relantionship between them as I posted in my fist answers that's why I'm choosing ID from table 1 and measure is on table 2.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |