cancel
Showing results for
Search instead 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 ,

For your requirement, please create the measure below.

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.
1 REPLY 1
Resident Rockstar

Hi @Anonymous ,

For your requirement, please create the measure below.

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.

## Helpful resources

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors