cancel
Showing results for
Did you mean:
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
Continued Contributor

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

Continued Contributor

@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.
Continued Contributor

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

Continued Contributor

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

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

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors