Coding checking and request help to multiple to value

[NickProp28]

Dear Community, 

I have two table and applied lookupvalue to get the information.

Condition:
If Unit is KG or LB, vlookup and return the KG rate.

If Unit is M3 or CF, vlookup and return the M3 rate.

Am I right if the DAX code is in this way? Just want double confirm on it

KG/M3 = Switch(True(),
(Main[Unit]= "KG" || Main[Unit] = "LB") , LOOKUPVALUE(Table1[Rate],Table1[From_Unit],Main[Unit],Table1[To_Unit],"KG"), 
(Main[Unit] ="M3" || Main[Unit] = "CF") , LOOKUPVALUE(Table1[Rate],Table1[From_Unit],Main[Unit],Table1[To_Unit],"M3"),
BLANK())

Furthermore, I would like to modify the DAX code (No idea how to apply to calculation after the conditional statement). Instead showing the rate, I want the final calculation of multiply rate and quatity.

Expected value:

Quantity	Unit	KG/M3
125.2	KG	125.2
1023.1	LB	464.070
33	M3	33
1234	CF	34.943
0	KG	0
0	LB	0
0	M3	0
0	CF	0

Could you please have a look at this pbix: https://ufile.io/mfspjog4

Appreciated any help provided. 

[amitchandak]

@NickProp28 , Are you trying move data from one table to another?

Check if this video can help how to do -https://www.youtube.com/watch?v=czNHt7UXIe8

[NickProp28]

Dear @amitchandak ,

No Sir, 

I want to include the calculation part in SWITCH after return the rate. 

For example, 

(Main[Unit]= "KG" || Main[Unit] = "LB") , LOOKUPVALUE(Table1[Rate],Table1[From_Unit],Main[Unit],Table1[To_Unit],"KG"), Quantity * KG/M3 

 * I know can simply open another new column and perform the calculation. But would like to ask is it possible to do it in SWITCH.

[v-yiruan-msft · Accepted solution]

Hi @NickProp28 ,

You can update the formula of your calculated column [KG/M3] as below, the part with red font is new added:

KG/M3 =
SWITCH (
    TRUE (),
     ( Main[Unit] = "KG"
        || Main[Unit] = "LB" ),
        LOOKUPVALUE (
            Table1[Rate],
            Table1[From_Unit], Main[Unit],
            Table1[To_Unit], "KG"
        ) * 'Main'[Quantity],
     ( Main[Unit] = "M3"
        || Main[Unit] = "CF" ),
        LOOKUPVALUE (
            Table1[Rate],
            Table1[From_Unit], Main[Unit],
            Table1[To_Unit], "M3"
        ) * 'Main'[Quantity],
    BLANK ()
)

Best Regards

[amitchandak]

Hi @NickProp28 ,

I have checked your file. And you can use switch.

I created two new columns without switch or if and they giving values as an individual, which gets merged because of the switch in your column.

At a high level, the approach seems fine. Unless there some issue you are facing.