Forum Discussion

WDW's avatar
WDW
New Member
5 years ago
Solved

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

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...
Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

    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])

    )

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

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])