cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rampeur
Frequent Visitor

Vlookup

Hi Guys,

 

I would need some help from you

 

I have two Table:

 

TABLE 1 with Hotel Transactions:

 

POINT OF SALESCITY CODECOUNTRY CODERATE PAID
FRMRSFR130
FRNYCUS260
USLAXUS280

 

TABLE 2 with HOTEL RATE CITY CAP:

 

POINT OF SALESCITY CODECOUNTRY CODERATE CAP
FR FR160
USNYCUS300
US US290

 

 

I would like a add the column RATE CAP to the TABLE 1.

 

The resulats depends on 3 variables:

POS, CITY CODE and COUNTRY CODE.

 

But is some cases in TABLE 2, you see that there is no city code, meaning that the RATE CAP is applying in all cities in the country.

 

Because of the blank, i dont know how to create my vlookup as i cant create a KEY.

 

Can you guys help me out?

 

Thank you in advance

 

Maxime

1 ACCEPTED SOLUTION

Hi @Rampeur,

 

Try this calculated column:

 

RATE CAP = 
VAR vPOS = Table1[POINT OF SALES]
VAR vCityCode = Table1[CITY CODE]
VAR vCountryCode = Table1[COUNTRY CODE]
VAR vLookupWithCity =
    LOOKUPVALUE (
        Table2[RATE CAP],
        Table2[POINT OF SALES], vPOS,
        Table2[COUNTRY CODE], vCountryCode,
        Table2[CITY CODE], vCityCode
    )
VAR vLookupWithoutCity =
    LOOKUPVALUE (
        Table2[RATE CAP],
        Table2[POINT OF SALES], vPOS,
        Table2[COUNTRY CODE], vCountryCode,
        Table2[CITY CODE], BLANK ()
    )
VAR vResult =
    IF ( ISBLANK ( vLookupWithCity ), vLookupWithoutCity, vLookupWithCity )
RETURN
    vResult

 

DataInsights_0-1615504687088.png

 

Note: I changed POINT OF SALES to US for the NYC row. Let me know if that is incorrect.

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Is this the result you are expecting?

= COALESCE(CALCULATE(MAX('Rate cap'[RATE CAP]),FILTER('Rate cap','Rate cap'[POINT OF SALES]=EARLIER(Transactions[POINT OF SALES])&&'Rate cap'[CITY CODE]=EARLIER(Transactions[CITY CODE])&&'Rate cap'[COUNTRY CODE]=EARLIER(Transactions[COUNTRY CODE]))),CALCULATE(MAX('Rate cap'[RATE CAP]),FILTER('Rate cap','Rate cap'[POINT OF SALES]=EARLIER(Transactions[POINT OF SALES])&&'Rate cap'[CITY CODE]<>EARLIER(Transactions[CITY CODE])&&'Rate cap'[COUNTRY CODE]=EARLIER(Transactions[COUNTRY CODE]))))

Untitled.png


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

@Rampeur - You can use LOOKUPVALUE or MAXX(FILTER(...),...)


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Hi Greg, for sure I have already tried...

and this is not working. Because it creates duplicate values.
For example, how would it work for the 3rd line of the table 1?


Hi @Rampeur,

 

Try this calculated column:

 

RATE CAP = 
VAR vPOS = Table1[POINT OF SALES]
VAR vCityCode = Table1[CITY CODE]
VAR vCountryCode = Table1[COUNTRY CODE]
VAR vLookupWithCity =
    LOOKUPVALUE (
        Table2[RATE CAP],
        Table2[POINT OF SALES], vPOS,
        Table2[COUNTRY CODE], vCountryCode,
        Table2[CITY CODE], vCityCode
    )
VAR vLookupWithoutCity =
    LOOKUPVALUE (
        Table2[RATE CAP],
        Table2[POINT OF SALES], vPOS,
        Table2[COUNTRY CODE], vCountryCode,
        Table2[CITY CODE], BLANK ()
    )
VAR vResult =
    IF ( ISBLANK ( vLookupWithCity ), vLookupWithoutCity, vLookupWithCity )
RETURN
    vResult

 

DataInsights_0-1615504687088.png

 

Note: I changed POINT OF SALES to US for the NYC row. Let me know if that is incorrect.

View solution in original post

Thank you so much, it works well! 🙂

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!