Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I am trying to find a way to subtract values from the same column ('Output') based on filters.
E.g., for device serial number 'A123BX90', I would like to find the difference of 'Output Check' from [TestStep] column between 'Process-Pre' & 'Process-Post' [TestProcess]. Likewise for 'Measured Value' & 'Final Output' from TestStep Column.
Could you please guide me how to achieve the below via DAX (TestProcess name is dynamic and will change throughout entire dataset and I can't hardcode it based on the current names) ?
'Output Check' Difference = Output [Process-Pre] - Output [Process-Post] (124.88-120.52)
DeviceSerialNo | TestStep | TestProcess | Output |
A123BX90 | Initialization | Process-Pre | Null |
A123BX90 | Condition Check | Process-Pre | Pass |
A123BX90 | Matching check | Process-Pre | Pass |
A123BX90 | Input Test | Process-Pre | Null |
A123BX90 | Routine Initialization | Process-Pre | Pass |
A123BX90 | Output Check | Process-Pre | 124.88 |
A123BX90 | Measured value | Process-Pre | -35 |
A123BX90 | Final Output | Process-Pre | 24.34 |
A123BX90 | End Test | Process-Pre | Not Set |
A123BX90 | Initialization | Process-Post | Null |
A123BX90 | Condition Check | Process-Post | Pass |
A123BX90 | Matching check | Process-Post | Fail |
A123BX90 | Input Test | Process-Post | - |
A123BX90 | Routine Initialization | Process-Post | Pass |
A123BX90 | Output Check | Process-Post | 120.52 |
A123BX90 | Measured value | Process-Post | -60 |
A123BX90 | Final Output | Process-Post | 25.86 |
A123BX90 | End Test | Process-Post | Not Set |
Thanks in advance!
Solved! Go to Solution.
Hi, @DreamToGet
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
In Power Query, you may create a new query with the following m codes in 'Advanced Editor'.
let
Source = Table.Group(Table, {"DeviceSerialNo", "TestStep"}, {{"Data", each
let
x=try Number.From([Output]{0}) otherwise null,
y=try Number.From([Output]{1}) otherwise null
in x-y , type number}})
in
Source
Result:
If you want to use DAX, you need to create an index column in Power Query.
You may create a measure as below.
Result Measure =
SUMX(
SUMMARIZE(
'Table',
[DeviceSerialNo],
[TestStep],
"Result",
var minindex = MIN('Table'[Index])
var maxindex = MAX('Table'[Index])
var val1 =
IFERROR(
VALUE(
MAXX(
FILTER(
'Table',
[Index]=minindex
),
[Output]
)
),
BLANK()
)
var val2 =
IFERROR(
VALUE(
MAXX(
FILTER(
'Table',
[Index]=maxindex
),
[Output]
)
),
BLANK()
)
return
val1-val2
),
[Result]
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @DreamToGet
In Power Query, you may add a new step with the following m codes. The pbix file is attached in the end.
= Table.Group(#"Changed Type", {"DeviceSerialNo", "TestStep"}, {{"Data", each
let
x= Table.Max( Table.SelectRows(_,each [TestProcess]="Process-Pre"),"StartDate")[Output],
y= Table.Max( Table.SelectRows(_,each [TestProcess]="Process-Post"),"StartDate")[Output],
m= try Number.From(x) otherwise null,
n= try Number.From(y) otherwise null
in m-n
}})
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @DreamToGet
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
In Power Query, you may create a new query with the following m codes in 'Advanced Editor'.
let
Source = Table.Group(Table, {"DeviceSerialNo", "TestStep"}, {{"Data", each
let
x=try Number.From([Output]{0}) otherwise null,
y=try Number.From([Output]{1}) otherwise null
in x-y , type number}})
in
Source
Result:
If you want to use DAX, you need to create an index column in Power Query.
You may create a measure as below.
Result Measure =
SUMX(
SUMMARIZE(
'Table',
[DeviceSerialNo],
[TestStep],
"Result",
var minindex = MIN('Table'[Index])
var maxindex = MAX('Table'[Index])
var val1 =
IFERROR(
VALUE(
MAXX(
FILTER(
'Table',
[Index]=minindex
),
[Output]
)
),
BLANK()
)
var val2 =
IFERROR(
VALUE(
MAXX(
FILTER(
'Table',
[Index]=maxindex
),
[Output]
)
),
BLANK()
)
return
val1-val2
),
[Result]
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-alq-msft ,
This is exactly what I was looking for. Thank you very much for this.
However I have another issue now. There are cases where same 'TestProcess' has been repeated multiple times and I would like to use the latest results (based on StartDate column) to calculate difference but I am unable to do so. Could you please assist on this?
I have attached my latest pbix file along with updated DAX for your reference.
Subtract values from same column based on multiple filters.pbix
Thanks!
Hi, @DreamToGet
In Power Query, you may add a new step with the following m codes. The pbix file is attached in the end.
= Table.Group(#"Changed Type", {"DeviceSerialNo", "TestStep"}, {{"Data", each
let
x= Table.Max( Table.SelectRows(_,each [TestProcess]="Process-Pre"),"StartDate")[Output],
y= Table.Max( Table.SelectRows(_,each [TestProcess]="Process-Post"),"StartDate")[Output],
m= try Number.From(x) otherwise null,
n= try Number.From(y) otherwise null
in m-n
}})
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |