topic How to dynamically change the max value in the gauge visual? in DAX Commands and Tips

How to dynamically change the max value in the gauge visual?

euro_sree — Thu, 02 Jan 2025 16:51:38 GMT

My daily target turnover is $270000. I have a slicer for the year. The data is from 01/01/2023. I want when only the year 2023 is slected in the slicer the maximum value should come as 365 * 270000 if only 2024 is selected from the slicer then the maximum value should come 365 * 270000 if both the years are selected then 730 * 270000 and so on. If in case nothing is selected in the slicer then it should calculate (Today() - date(2023,1,1)) * 270000

Can somebody please help me to achieve this

Re: How to dynamically change the max value in the gauge visual?

FarhanJeelani — Thu, 02 Jan 2025 18:17:12 GMT

Hi euro_sree ,

To achieve your requirement, you can create a DAX measure that dynamically calculates the maximum value based on the selected year(s) in the slicer. Here’s how to do it:

Steps:

Assume You Have a Date Table:
- Make sure your data model includes a properly set up Date table, with a relationship to your fact table containing turnover data.

Create the Measure:

Use the following DAX code to create the measure:

Maximum Target Turnover = 
VAR SelectedYears = VALUES(DateTable[Year]) -- Get the selected years
VAR TotalDays =
    IF(
        ISFILTERED(DateTable[Year]),
        SUMX(SelectedYears, 365), -- Calculate total days for selected years
        TODAY() - DATE(2023, 1, 1) -- Days from 01/01/2023 to today
    )
RETURN
    TotalDays * 270000

Explanation:
- VALUES(DateTable[Year]): Captures the selected years from the slicer.
- SUMX(SelectedYears, 365): Calculates the total days for all selected years (365 days per year).
- ISFILTERED(DateTable[Year]): Checks if the slicer has any filter applied; if not, it defaults to calculating the days from 01/01/2023 to TODAY().
- 270000: The daily target turnover is multiplied by the total days.
Add the Measure to Your Visual:
- Use the measure in a card or visual to display the calculated maximum target turnover.
Test the Functionality:
- Select different years in the slicer and verify that the calculation changes accordingly.
- If nothing is selected in the slicer, ensure the calculation uses (Today() - DATE(2023,1,1)) * 270000.

This measure is dynamic and should meet your requirement for handling both slicer selections and the default case when nothing is selected. Let me know if you need further adjustments! 😊

Please mark this as solution if it helps. Appreciate Kudos.

Re: How to dynamically change the max value in the gauge visual?

euro_sree — Fri, 03 Jan 2025 04:23:14 GMT

Hi Farhan

Thank you so much for the solution and appreciate the way its been explained.

I will try this today and update you.

Regards

Re: How to dynamically change the max value in the gauge visual?

Anonymous — Fri, 03 Jan 2025 06:15:42 GMT

Hi euro_sree ,

The method FarhanJeelani provided should be helpful.

Besides, you can also try the following DAX formula to calculate the max value based on slicer.

Maximum Target Turnover = VAR SelectedYears = VALUES('Table'[Year]) VAR StartDate = DATE(2023, 1, 1) VAR TodayDate = TODAY() RETURN IF ( ISFILTERED('Table'[Year]), CALCULATE(COUNTROWS('Table') * 270000), DATEDIFF(StartDate, TodayDate, DAY) * 270000 )

Best Regards,

Wisdom Wu

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