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 III
Super User III

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 IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors