cancel
Showing results for 
Search instead for 
Did you mean: 
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

Accepted Solutions
Super User
Super User

Re: Prioritize what value gets picked up in vlookup

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

 

 

 



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

Proud to be a Datanaut!




View solution in original post

6 REPLIES 6
Super User
Super User

Re: Prioritize what value gets picked up in vlookup

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

 

 

 



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

Proud to be a Datanaut!




Anonymous
Not applicable

Re: Prioritize what value gets picked up in vlookup

Hi @MFelix,

 

Thank you for your response. 

 

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

Super User
Super User

Re: Prioritize what value gets picked up in vlookup

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?


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

Proud to be a Datanaut!




Anonymous
Not applicable

Re: Prioritize what value gets picked up in vlookup

yes, for instance if there is a match for both Rankin1 and Ranking6, I want it to pick Ranking6. 

Super User
Super User

Re: Prioritize what value gets picked up in vlookup

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

 

 

 



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

Proud to be a Datanaut!




View solution in original post

Super User
Super User

Re: Prioritize what value gets picked up in vlookup

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 140 members 1,470 guests
Please welcome our newest community members: