Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Prioritize what value gets picked up in vlookup

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?

Lookup_PBI.png

 

 

Any help would be appreciated.

 

Thank you.

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

You can do the following:

  • Create a new table with the Ranking values and sort order in my example I added the following:

Rank       Sort

Rank6 1
Rank5 2
Rank3 3
Rank1 4
Rank2 5
Rank4 6

 

  • Add a column to your table with full details with the following code:

 

Rank_Sort = LOOKUPVALUE('Rank'[Sort];'Rank'[Rank];Table2[Ranking])

 

  • This column is auxiliary to get the Ranking sorting number
  • Add the following measure:
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.

 

Ranking.png

 

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

Hi @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,

 

Ranking.gif

 

Regards,

MFelix

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix,

 

Thank you for your response. 

 

The actual values are not really Rank1,2,3 and sorting them alphabetically would not work.

What is the.order in wich you.want to get the values of.rank? Do you want to have a.custom ranking based on the values.of.your ranking column?

Example the order of.the ranking is the.one.below

Ranking5
Ranking6
Rankin1
Ranking3
Ranking4
Ranking2

So when.you look at your table if A as ranking5 return it if not returns ranking6 and so on. Is this it?

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

yes, 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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Anonymous,

 

You can do the following:

  • Create a new table with the Ranking values and sort order in my example I added the following:

Rank       Sort

Rank6 1
Rank5 2
Rank3 3
Rank1 4
Rank2 5
Rank4 6

 

  • Add a column to your table with full details with the following code:

 

Rank_Sort = LOOKUPVALUE('Rank'[Sort];'Rank'[Rank];Table2[Ranking])

 

  • This column is auxiliary to get the Ranking sorting number
  • Add the following measure:
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.

 

Ranking.png

 

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.