Create Measure and Dynamically Return MAX Value from Filtered Rows with DAX

[lulubyte]

I am trying to create a measure to return the maximum TimeId grouped by the ScenarioId illustrated in the table below.

Users can filter this table with a slicer on the ActualValue column to return only the rows that meet a certain threshold. From there I need to find the maximum TimeId by ScenarioId so I can use that measure to filter another table.

This cannot be precalculated as the user will be able to filter which rows they see.

Is something like this possible? I've been trying for a few hours but cannot get anything to work. I could do this in SQL in a few seconds with a subquery, but we need users to be able to filter ad-hoc.

ScenarioId	UnitId	TimeId	ActualValue
1	A	1	10
1	A	4	15
1	B	1	5
1	C	5	10
2	A	2	15
2	A	6	20
2	B	3	10
2	B	5	15

[lulubyte]

Thanks for the reply.

For some reason the ALLEXCEPT seems to disregard the slicer to filter the TargetValue column. The ALLEXCEPT returns the maximum TimeId, but it doesn't apply it to the rows filtered from the slicer...

[NaveenGandhi]

@lulubyte 

Can you share the sample data and expected output? So that it will be easy to understand the problem.

[lulubyte]

Sure, if a user selected the value 15 in the ActualValue slicer then the following data would be returned if the ScenarioId and TimeId columns were placed into a table visual:

ScenarioId	TimeId
1	4
2	5

The way I would think of this as a workflow would be:

1. The ActualValue slicer first filters the table to show only rows where the ActualValue column is equal to 15.
2. Then the measure would find the maximum TimeId from those rows.

[NaveenGandhi · Accepted solution]

@lulubyte 

Below measure should do the work. This will work when some value is selected in the slicer.

Max_timeid = CALCULATE(max('Table'[TimeId]),ALLEXCEPT('Table','Table'[ScenarioId],'Table'[ActualValue]))

Let me know if this helps!

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

[lulubyte]

That did the trick! Thank you so much for your help, it has saved me quite a bit of time!

[NaveenGandhi]

Hello @lulubyte 

Try this Measure. 

Max_timeid = CALCULATE(max('Table'[TimeId]),ALLEXCEPT('Table','Table'[ScenarioId]))

Let me know if this helps!

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!