New Column or Measure

[AO1]

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 conditions. 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. Thank you!

[slorin]

Hi,

Power Query solution :

let
Source = YourSource,
Group = Table.Group(Source, {"Parameter"}, 
  {{"Data", each _, type table [Parameter=text, Treatment=text, Score=number]},
   {"New_Column", each _{[Treatment="CON"]}[Score], type number}}),
Expand = Table.ExpandTableColumn(Group, "Data", {"Treatment", "Score"}, {"Treatment", "Score"})
in
Expand

Stéphane 

[AO1]

@slorin, thank you!

[wdx223_Daniel · Accepted solution]

DAX:

CalculatedColumn=MAXX(FILTER(Table,Table[Parameter]=EARLIER(Table[Parameter])&&Table[Treatment]="CON"),Table[Score])

Measure=CALCULATE(MAX(Table[Score]),ALLEXCEPT(Table,Table[Parameter]=MAX(Table[Parameter]),Table[Treatment]="CON")

[AO1]

@wdx223_Daniel, your CalculatedColumn formula works flawlessly, and i've used it in different projects. Thank you.

I could not get the Measure formula to work however. I'm currently in need of a measure rather than a calculated column.Yes, the formula is missing a closing bracket, the addition of this bracket did not make it work. I will appreciate if you have a solution for me. Thanks.

[wdx223_Daniel]

Measure=CALCULATE(MAX(Table[Score]),Table[Parameter]=MAX(Table[Parameter]),Table[Treatment]="CON")

[AO1]

Now, it works. Thanks!

[AO1]

@wdx223_Daniel, you made my day! Your solution not only works, your code is concise and easy to follow. Thank you!