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





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

Proud to be a Super User!




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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.





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

Proud to be a Super User!




Thank you so much, it works well! 🙂

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.