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

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

  • v-alq-msft's avatar
    v-alq-msft
    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.

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.

    • 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
        Community 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.