Have calculations only work in the filtered context

[Kalibur]

I hope this is a simple question and I don't know what to actually google. This is just a sample of what I actually want to do but this functionality applies to so many things i want to do. 

I have a table that has 2 columns. [TestName, Log Time]

Every test ran for a different period of time and my goal is to calculate how much time is left in the test at any specific log time. I'm making a line graph over time. TimeRemaining vs LogTime.

The formula for this is simple TimeRemaining = Max(LogTime) - LogTime.

The issue that Max(LogTime) for the data set is larger than Max(LogTime) for any particularly TestName that i'm filtering to. When i filter my line graph to an individual test name how do I ensure that the filter applies to the TimeRemaining calculation? As you can see in the picture below the TimeRemaining is not 0 at the end of this test since the context of my filter is not correctly applied to the calculation.  

[v-kaiyue-msft]

Hi @Kalibur ,

You can try adjusting the expression to:

TimeRemaining=
CALCULATE(
MAX('Table'[Log Time]) - 'Table'[Log Time],
ALLEXCEPT('Table', 'Table'[TestName])
)

The ALLEXCEPT function removes all contextual filters from the table except those that have been applied to the specified column. More details can be found in the documentation: ALLEXCEPT function (DAX) - DAX | Microsoft Learn.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Kalibur · Accepted solution]

hi @v-kaiyue-msft 

I wasn't aware of the AllExcept function. That seems useful. Unfortunately your suggestion didn't solve the problem. It wants me to aggregate the second log time. On the other hand the Max(LogTime) is returning the max log time based on my testname filter. 

"A single value for column "Log Time" in table cannot be determined. Ths can happen when a measure formula refers to a column that contains many values without specifying an aggregation....." 

Update: you got me really close. I ended up getting it to work with this equation. 

TimeRemaining =

VAR MaxLogTimeForTest = CALCULATE(MAX([Log Time]), ALLEXCEPT(combined_output, combined_output[Test Name]))

RETURN

MaxLogTimeForTest - [Log Time]