Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])
)
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.
Now that you have added the factor you can simplify the formula even more:
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])
)
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.