Iterate through values to create a new column based on lookup from another table

[WDW]

I have a table named ChanData with 3 columns: timestamp, ChanID, Value. The data in the value column is the metric (si) value for the associated ChanID I have a second table named ConvFactor that has 4 columns: From_Unit, To_Unit, Conv(the conversion rate), Temp Add (formula for temperature conversion is (Value*1.8)+32). There is a third table: ChanLookUp that creates a relationship based on the From_Unit value to identify the correct conversion factor(Conv) for the ChanID.

 I need to create a 4th column (Imperial) in the Data table that contains the converted value in Imperial Units, the end result would look like:

timestamp          ChanID Value    Imperial

2021-01-18 1:45:22 PM 104         0.47        47

2021-01-18 1:46:20 PM 106         80           176

2021-01-18 1:47:15 PM 107         1020       148

  I have a current solution that is working using switch as follows:

Imperial = SWITCH(

TRUE(),

[ChanID] = 100, [Value] * RELATED(ConvFactor[Conv]),

[ChanID] = 101, [Value] * RELATED(ConvFactor[Conv]),

[ChanID] = 102, [Value] * RELATED(ConvFactor[Conv]),

[ChanID] = 103, [Value] * RELATED(ConvFactor[Conv]),

[ChanID] = 104, [Value] * RELATED(ConvFactor[Conv]),

[ChanID] = 105, [Value] * RELATED(ConvFactor[Conv]),

[ChanID] = 106, [Value] * RELATED(ConvFactor[Conv])+(32),

[ChanID] = 107, [Value] * RELATED(ConvFactor[Conv])

)

I need help to create a solution that will will iterate through the ChanID by row and apply the conversion. My ChanID list is large (1000+) and the SWITCH solution is very tedious to work with. I have searched and read through many posts, however, I have not been able to put together anything that works.

I am a Power BI noob and any help would be much appreciated. Thank you

[lbendlin]

you use either switch or if - not both. Which one you use is up to preference. Switch mostly makes sense if you have multiple decisions to make.

Imperial =

SWITCH(

RELATED(ConvFactor[Temp Add]) = 1,

[Value] * RELATED(ConvFactor[Conv])+(32),

[Value] * RELATED(ConvFactor[Conv])

)

Now that you have added the factor you can simplify the formula even more:

Imperial =RELATED(ConvFactor[Temp Add]) *32 +[Value] * RELATED(ConvFactor[Conv])

[lbendlin · Accepted solution]

Let the data model do the work for you. What are your tables linked on?  Is the correction factor per channel ID stored somewhere?

You can simplify your switch() statement like this:

Imperial = SWITCH(

[ChanID],106, [Value] * RELATED(ConvFactor[Conv])+(32),

[Value] * RELATED(ConvFactor[Conv])

)

[WDW]

  Thank you very much for your help, this mostly works for me. I apologize for the delayed reply. I have a lot to learn.  I left out an important detail in my original post -- approx 30% of my channels require the temperature adjustment which is identified in the Temp Add column of the ConvFactor table. I need to add an IF statement that looks for a 1 (True) to know to make the addition to the temperature convertion when required. This is what I have attempted:

Imperial =

SWITCH(

IF(RELATED(ConvFactor[Temp Add]) = 1,

[Value] * RELATED(ConvFactor[Conv])+(32),

[Value] * RELATED(ConvFactor[Conv]))

)

I get an error saying I do not have enough arguments for the SWITCH function

I was able to sort it out, I used the IF statement without the SWITCH and it is working as expected. Thank you for setting me on the right path.