Forum Discussion

DreamToGet's avatar
DreamToGet
Frequent Visitor
5 years ago
Solved

Subtract values from same column based on multiple filters

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 'Outpu...
Need Help
DreamToGet's avatar
DreamToGet
Frequent Visitor
5 years ago

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!

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
5 years ago

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.