cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

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

1 ACCEPTED SOLUTION
Super User III
Super User III

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

)

 

View solution in original post

3 REPLIES 3
Super User III
Super User III

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])
Super User III
Super User III

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

)

 

View solution in original post

  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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors