topic Re: Unable to mutiply lookup value in measure in DAX Commands and Tips

Unable to mutiply lookup value in measure

Anonymous — Fri, 25 Nov 2022 02:30:50 GMT

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 :

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

Re: Unable to mutiply lookup value in measure

Anonymous — Fri, 25 Nov 2022 09:24:17 GMT

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

Re: Unable to mutiply lookup value in measure

Anonymous — Mon, 28 Nov 2022 03:02:45 GMT

Hi Anonymous , 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 :

Re: Unable to mutiply lookup value in measure

Anonymous — Tue, 29 Nov 2022 03:27:42 GMT

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

Thanks