topic Re: New Column or New Measure in DAX Commands and Tips

New Column or New Measure

AO1 — Tue, 21 Nov 2023 18:49:18 GMT

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!

Re: New Column or New Measure

lbendlin — Tue, 21 Nov 2023 20:12:14 GMT

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

Re: New Column or New Measure

AO1 — Thu, 23 Nov 2023 17:42:16 GMT

lbendlin , thank you!

Re: New Column or New Measure

Mk60 — Wed, 29 Nov 2023 05:00:35 GMT

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.