Forum Discussion
New Column or 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 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!
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")
7 Replies
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")
wdx223_Daniel, you made my day! Your solution not only works, your code is concise and easy to follow. Thank you!
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.
Measure=CALCULATE(MAX(Table[Score]),Table[Parameter]=MAX(Table[Parameter]),Table[Treatment]="CON")
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
ExpandStéphane
