cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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

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
1 ACCEPTED SOLUTION
Super User

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

6 REPLIES 6
New Member

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

Super User

@lulubyte

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

New Member

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.
Super User

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

New Member

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.