cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Calculated column returning a single value based on filters from two other columns

Hello,

I would like to make an operation (substraction) on values from a same column and then show it on a different column.

I developped a small example for better understanding of what I'm trying to do:

Raw Data:

 Tool # Operation Start Tool 1 A 5/12/2019 Tool 1 B 5/14/2019 Tool 1 C 5/18/2019 Tool 2 A 5/15/2019 Tool 2 B 5/18/2019 Tool 2 C 5/25/2019

What I'm trying to calculate:

 Tool # Turn Around Time Tool 1 6 Tool 2 10

TAT = Diff between start time of operation C and start time of operation A

I could work on my raw data (excel spreadsheet) directly to work this out but I wanted to know if I could do something in POWER BI directly

Thanks for the help

Sebastien

1 ACCEPTED SOLUTION
Resident Rockstar

Hi @Anonymous ,

Measure =
VAR s_a =
CALCULATE (
MAX ( 'Table1'[Start] ),
FILTER ( ALLEXCEPT ( 'Table1', Table1[ool #] ), 'Table1'[Operation] = "A" )
)
VAR s_c =
CALCULATE (
MAX ( 'Table1'[Start] ),
FILTER ( ALLEXCEPT ( 'Table1', Table1[ool #] ), 'Table1'[Operation] = "C" )
)
RETURN
DATEDIFF ( s_a, s_c, DAY )

Here is the output.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Resident Rockstar

Hi @Anonymous ,

Measure =
VAR s_a =
CALCULATE (
MAX ( 'Table1'[Start] ),
FILTER ( ALLEXCEPT ( 'Table1', Table1[ool #] ), 'Table1'[Operation] = "A" )
)
VAR s_c =
CALCULATE (
MAX ( 'Table1'[Start] ),
FILTER ( ALLEXCEPT ( 'Table1', Table1[ool #] ), 'Table1'[Operation] = "C" )
)
RETURN
DATEDIFF ( s_a, s_c, DAY )

Here is the output.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements