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

Frequent Visitor

## How can I make my measures dynamic with a slicer?

Hello,

I have a basic dashboard in which I want to calculate the running total of a set of values, calculate the high water mark and calculate the difference from the high water mark. This is a snapshot of my dashboard.

The data comprises values from two data sets and I want the calculations to update based on the selection of each individual data set or both combined.

I have created a measure to calculate the Running Total as below. This calculation works.

Running Total =
CALCULATE (
[Total Values],
FILTER ( ALL ( 'Data' ), Data[Index] <= MAX ( Data[Index] ) )
)

The High Water Mark is the highest value in the running total until a new high is reached. For example:

• if the 1st value makes the Running Total equal 10 then the High Water Mark will be set to 10;
• if the 2nd value makes the Running Total equal 5 then the High Water Mark will remain as 10;
• if the 3rd value makes the Running Total equal 15 then the High Water Mark will be set to 15;

I have created a measure to calculate the High Water Mark as below. This calculation works.

High Water Mark =
MAXX (
FILTER ( ALL ( 'Data' ), Data[Index] <= MAX ( Data[Index] ) ),
"Running Total", [Running Total]
),
[Running Total]
)

The Difference from High Water Mark is the difference between the High Water Mark and the Runnnig Total. I have created a measure to calculate this as below. This calculation works.

Difference from High Water Mark = [Running Total] - [High Water Mark]

The challenge I have is that when I select a single data set the Running Total, High Water Mark & Difference from High Water Mark calculations remain based on the combined data set (Dataset1 & Dataset2).

I have an index column to order the individual data values as there are instances where individual data values ocurr on the same date. I wondered if the solution was to somehow create a dynamic measure which would be either an index calendar which would comprise the individual data values from the selected data set which I could then reference in my Running Total and High Water Mark measures.

I am not an experienced user of Power BI and I am unable to resolve this issue with my curent level of knowledge therefore if anyone in the community can help I would greatly appreciate it.

I don't know how to attach my PBIX template but this is a wetransfer link to it...https://we.tl/t-LSBoXYMD3i

Thank you for any help you can provide.

Neil

1 ACCEPTED SOLUTION
Frequent Visitor

Hi @DataNinja777 , thank you for your response.

I didn't need to refer to the link you kindly shared as I realised that in my filters I had selected ALL instead of ALLSELECTED. I changed to ALLSELECTED and it works as intended.

Thanks,

Neil

2 REPLIES 2
Super User

A widely used technique to make the measures dynamic with a slicer selection is to create a harvester measure in combination with a disconnected slicer created from a disconnected table.  I attach a classic article on this topic.

Best regards,

Frequent Visitor

Hi @DataNinja777 , thank you for your response.

I didn't need to refer to the link you kindly shared as I realised that in my filters I had selected ALL instead of ALLSELECTED. I changed to ALLSELECTED and it works as intended.

Thanks,

Neil

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.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors