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
Anonymous
Not applicable

Unable to mutiply lookup value in measure

Hi all, need some help as I am unable to mutiply value that lookup to another table . Here are the measure created :

 

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([4.Daily Energy Chiller 2] = BLANK(), 0 , [4.Daily Energy Chiller 2] * first_rate)
var price_second_rate = IF([4.Daily Energy Chiller 2] = BLANK(), 0 ,([4.Daily Energy Chiller 2] - 200) * second_rate)
var total =  IF([4.Daily Energy Chiller 2] = BLANK() || [4.Daily Energy Chiller 2] < 200  , price_first_rate,  price_first_rate + price_second_rate )
return total


result i am getting : 

Hairul_Khumaini_0-1669343165609.png


result i am expecting :

Hairul_Khumaini_1-1669343254995.png


table that i lookup :

Hairul_Khumaini_2-1669343303455.png

 

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

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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:

vxinruzhumsft_0-1669368219292.png

 

 

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.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi all, turn out I linked the wrong relationship . After linking with month instead of date all daily data are working now.

Thanks

v-xinruzhu-msft
Community Support
Community Support

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:

vxinruzhumsft_0-1669368219292.png

 

 

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.

Anonymous
Not applicable

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 :

Hairul_Khumaini_2-1669604628498.png

 

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.

Top Solution Authors