Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DreamToGet
Frequent Visitor

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 '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)

 

DeviceSerialNoTestStepTestProcessOutput
A123BX90InitializationProcess-PreNull
A123BX90Condition CheckProcess-PrePass
A123BX90Matching checkProcess-PrePass
A123BX90Input TestProcess-PreNull
A123BX90Routine InitializationProcess-PrePass
A123BX90Output CheckProcess-Pre124.88
A123BX90Measured valueProcess-Pre-35
A123BX90Final OutputProcess-Pre24.34
A123BX90End TestProcess-PreNot Set
A123BX90InitializationProcess-PostNull
A123BX90Condition CheckProcess-PostPass
A123BX90Matching checkProcess-PostFail
A123BX90Input TestProcess-Post-
A123BX90Routine InitializationProcess-PostPass
A123BX90Output CheckProcess-Post120.52
A123BX90Measured valueProcess-Post-60
A123BX90Final OutputProcess-Post25.86
A123BX90End TestProcess-PostNot Set

 

Thanks in advance!

2 ACCEPTED SOLUTIONS
v-alq-msft
Community Support
Community Support

Hi, @DreamToGet 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

f1.png

 

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:

f2.png

 

If you want to use DAX, you need to create an index column in Power Query.

f3.png

 

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:

f4.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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:

c1.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-alq-msft
Community Support
Community Support

Hi, @DreamToGet 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

f1.png

 

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:

f2.png

 

If you want to use DAX, you need to create an index column in Power Query.

f3.png

 

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:

f4.png

 

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:

c1.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.