Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    Hi Anonymous,

     

    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

     

     

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

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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?


      • DataInsights's avatar
        DataInsights
        Super User
        5 years ago

        Hi Anonymous,

         

        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

         

         

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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]))))