Calculating a Column based on multiple criteria

[mdykes]

Hi guys, I'm reasonably new to Power BI DAX code and am wondering how someone would approach this issue/help me out?

I am trying to calculate a column based on a multiple criteria in Power Bi. The issue is the value must be the result from one of four columns and based on a fifth column. 

The problem:

I am requiring an Emission rate (similar to 272.05) based on an Engine Tier and a Engine Power factor (kW). This Emission rate must first be calculated by looking up the corresponding Engine Power factor (kW) and then looking up 4 different column names that contain the desired Emission rate based on the Engine Tier.

For Example (see below), the emission rates are broken into 4 different columns based on their engine Tier and then the corresponding Engine Power factor (kW). 

However, the column that I create will need to lookup the Engine Power Factor (kW) and Engine Tier Columns (1, 2, 3 or 4) then produce an Emission rate based of those two pieces of criteria (see below for example). Although, how do I establish the result from 4 different columns? 

I have previously complete this on Excel using a Match () function, but cannot seem to be able to do it on Power Bi. 

I am wodnering if I use a lookupvalue and an Iferror to determine the value?

Help would be greatly appreciated. Thanks!!!

[v-eqin-msft · Accepted solution]

Hi @mdykes ,

I have reproduced some data sample according to the screenshot you provided and tranformed the table.

Here is the whole step:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQwVNJRMjI31TMzAjNM9MzM0RmmpkqxOiC1RiSoNSZBrQkJak1JUGtGnNpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Engine Power(kW)" = _t, Tile1 = _t, Tile2 = _t, Tile3 = _t, Tile4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Engine Power(kW)", Int64.Type}, {"Tile1", type number}, {"Tile2", type number}, {"Tile3", type number}, {"Tile4", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Engine Power(kW)"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Tile Type"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Tile","",Replacer.ReplaceText,{"Tile Type"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Engine Power(kW)", Int64.Type}, {"Tile Type", Int64.Type}, {"Value", type number}})
in
    #"Changed Type1"

Then you could use LOOKUPVALUE() function to create a column to find the matched value like this:

Emission rate =
LOOKUPVALUE ( 'Table'[Value], [Engine Power(kW)], [Eng], [Tile Type], [Tile] )

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mdykes]

I cannot post any tables with values in a reply segment. The blog is saying that there it is invalid and HTML was found in the message body. I can only reply with screenshots. 

[amitchandak]

@mdykes , Is the first screenshot and the second one are two table. if yes first one can be unpivoted and then you can split tire-based space in two columns.

https://radacad.com/pivot-and-unpivot-with-power-bi
Split -https://www.youtube.com/watch?v=FyO9Vmhcfag

Copy value in Dax from one table to another -https://youtu.be/czNHt7UXIe8

[mdykes]

Hi Amit,

The first screenshot is a seperate query compared to the second screenshot. Thus, I am trying to calculate across two seperate queries. Also, the values for the emission rates change very slightly with decimal places as we move down power factors, which I have forgotten to add in the first screenshot. Here it is in Excel (below), but not the entire 2000 rows for Engine Powers. Therefore, I am sorry but I am a tad confused when you specifiy to 'unpivot my first table and then split into tier based spce in two columns.' Alongside this, I don't really know what you're referring to when you link the dax from one table to another. 

I'm sorry, just may need a bit more clarification please!

[amitchandak]

@mdykes , can you please share all the table samples and output in a table format. You can copy-paste from excel to here.

[mdykes]

Would it be possible for me to send you the Engine Tier/Engine Power Factor Table with the accompanied Emission Rates as it is 2000 rows by 5 columns. I cannot insert into a reply thread. 

[mdykes]

Hi Amit,  below is screenshots of the table that contains the two look up criteriam  with the output calculated column on the left. Unfortunatly, I keep receiving an error when I try and paste the table into the reply segment.