Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Guys,
I would need some help from you
I have two Table:
TABLE 1 with Hotel Transactions:
POINT OF SALES | CITY CODE | COUNTRY CODE | RATE PAID |
FR | MRS | FR | 130 |
FR | NYC | US | 260 |
US | LAX | US | 280 |
TABLE 2 with HOTEL RATE CITY CAP:
POINT OF SALES | CITY CODE | COUNTRY CODE | RATE CAP |
FR | FR | 160 | |
US | NYC | US | 300 |
US | US | 290 |
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
Solved! Go to 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
Note: I changed POINT OF SALES to US for the NYC row. Let me know if that is incorrect.
Proud to be a 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]))))
@Rampeur - You can use LOOKUPVALUE or MAXX(FILTER(...),...)
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
Note: I changed POINT OF SALES to US for the NYC row. Let me know if that is incorrect.
Proud to be a Super User!
Thank you so much, it works well! 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |