Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all, need some help as I am unable to mutiply value that lookup to another table . Here are the measure created :
result i am getting :
result i am expecting :
table that i lookup :
If i manually add the value for first rate into the measure , i am able to produce expected result. Any idea on why i am unable to use variable to store and use the value that i want.
Thanks,
Hairul
Solved! Go to Solution.
Hi @Anonymous ,
I made some change to you code, here are the codes:
Measure = var first_rate = LOOKUPVALUE(Tariff_Info[Price(RM)],Tariff_Info[ID],1)
var second_rate = LOOKUPVALUE(Tariff_Info[Price(RM)],Tariff_Info[ID],2)
var price_first_rate = IF(SELECTEDVALUE('Table'[4.Daily Energy Chiller 2])=0,0,SELECTEDVALUE('Table'[4.Daily Energy Chiller 2])*first_rate)
var price_second_rate = IF(SELECTEDVALUE('Table'[4.Daily Energy Chiller 2]) = 0, 0 ,(SELECTEDVALUE('Table'[4.Daily Energy Chiller 2]) - 200) * second_rate)
var total = IF(SELECTEDVALUE('Table'[4.Daily Energy Chiller 2]) = 0|| SELECTEDVALUE('Table'[4.Daily Energy Chiller 2])< 200 , price_first_rate, price_first_rate + price_second_rate )
return total
Output:
Best Regards,
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi all, turn out I linked the wrong relationship . After linking with month instead of date all daily data are working now.
Thanks
Hi @Anonymous ,
I made some change to you code, here are the codes:
Measure = var first_rate = LOOKUPVALUE(Tariff_Info[Price(RM)],Tariff_Info[ID],1)
var second_rate = LOOKUPVALUE(Tariff_Info[Price(RM)],Tariff_Info[ID],2)
var price_first_rate = IF(SELECTEDVALUE('Table'[4.Daily Energy Chiller 2])=0,0,SELECTEDVALUE('Table'[4.Daily Energy Chiller 2])*first_rate)
var price_second_rate = IF(SELECTEDVALUE('Table'[4.Daily Energy Chiller 2]) = 0, 0 ,(SELECTEDVALUE('Table'[4.Daily Energy Chiller 2]) - 200) * second_rate)
var total = IF(SELECTEDVALUE('Table'[4.Daily Energy Chiller 2]) = 0|| SELECTEDVALUE('Table'[4.Daily Energy Chiller 2])< 200 , price_first_rate, price_first_rate + price_second_rate )
return total
Output:
Best Regards,
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xinruzhu-msft , thanks for updated code but can we lookup the same value mutiple time? I have mutiple table that I need to use the same measure but for different tables . My previous code works on the first table but not on other tables . I tried yours (on the second table ) and get value as below :
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |