Forum Discussion
New Column or New Measure
Hi,
I'm new to PowerBi and would really appreciate your help with creating either a new column or new measure. So i have the below table:
| Parameter | Treatment | Score |
| A | TRT1 | 10 |
| A | CON | 29 |
| A | TRT2 | 19 |
| A | TRT3 | 72 |
| A | TRT4 | 31 |
| B | TRT1 | 20 |
| B | CON | 15 |
| B | TRT2 | 31 |
| B | TRT3 | 41 |
| B | TRT4 | 13 |
| C | TRT1 | 54 |
| C | CON | 46 |
| C | TRT2 | 39 |
| C | TRT3 | 28 |
| C | TRT4 | 16 |
I want to create a new column or measure so that i have the new variable called "New_Column". The values in this new variable is the value for the "CON" treatment for each parameter.
| Parameter | Treatment | Score | New_Column |
| A | TRT1 | 10 | 29 |
| A | CON | 29 | 29 |
| A | TRT2 | 19 | 29 |
| A | TRT3 | 72 | 29 |
| A | TRT4 | 31 | 29 |
| B | TRT1 | 20 | 15 |
| B | CON | 15 | 15 |
| B | TRT2 | 31 | 15 |
| B | TRT3 | 41 | 15 |
| B | TRT4 | 13 | 15 |
| C | TRT1 | 54 | 46 |
| C | CON | 46 | 46 |
| C | TRT2 | 39 | 46 |
| C | TRT3 | 28 | 46 |
| C | TRT4 | 16 | 46 |
Please, how do i calculate this new column or measure using multiple conditional statement. Below are the conditions that needs to be met when calculating the value for each cell:
1. Subset only rows with same Parameter value as the cell being estimated.
2. From this Parameter subset, choose the Score for the Treatment named "CON"
I've realized i need multiple conditional statements such as this for several things other than the above example. I however run into the error that there is no single value for the formula i create. If both conditions listed above are met, only a single value would be generated for each individual cell of the New_Column, although each cell/row may have different values. I can do this fairly easily in Excel, but i really need to do the same in PowerBi. I will really appreciate help from PowerBi experts. PurpleWave22 lbendlin amitchandak
Thank you!
This doesn 't seem to need a measure. Even a calculated column is overkill. Much simpler to do in Power Query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcwxDoAgDIXhuzAz0FJURmXXxLAR7n8NS0mgxIUmXx5/KeY01uQ3Ax9wptou6bn5xTiAJ9gmi3g+O2ohPh5ErhlGN6SHIegJ/j61MC3SwuBF0gwHGtLDtOmJhKOWFsZDi4T5V/0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parameter = _t, Treatment = _t, Score = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parameter", type text}, {"Treatment", type text}, {"Score", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "New_Column", (k)=> Table.SelectRows(#"Changed Type",each [Parameter]=k[Parameter] and [Treatment]="CON")[Score]{0},Int64.Type) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
3 Replies
- lbendlin
Super User
This doesn 't seem to need a measure. Even a calculated column is overkill. Much simpler to do in Power Query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcwxDoAgDIXhuzAz0FJURmXXxLAR7n8NS0mgxIUmXx5/KeY01uQ3Ax9wptou6bn5xTiAJ9gmi3g+O2ohPh5ErhlGN6SHIegJ/j61MC3SwuBF0gwHGtLDtOmJhKOWFsZDi4T5V/0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parameter = _t, Treatment = _t, Score = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parameter", type text}, {"Treatment", type text}, {"Score", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "New_Column", (k)=> Table.SelectRows(#"Changed Type",each [Parameter]=k[Parameter] and [Treatment]="CON")[Score]{0},Int64.Type) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
- Mk60
Resolver I
My very first question, new to Power Bi. I am trying to find the way to create new measure or new column called "NewProduct" using text values from the existing column. So I tried to use simple If statement to select yellow values and name them "ProductA", and select several other blue highlighted values as "ProductB", and ignore the rest of the values in the existing column, but could not find dax expression for it. So what is the best way to use these parameters from my existing column to create new values in a new column. Much apprecite any help.
