Dynamic MAXX from Parameter selection

[Foxxon28]

Dear reader,

In Power BI I want to create a measure that dynamically calculates the MAX value of a given selection, so that I can dynamically increase the Y axis on a column chart.

Both the column value AND X axis are filled with parameters.

x Axis = Column names from different dimentions (Location / ProductType / Salesperson / etc)

Column value = [Measure 1], [Measure 2]

Normally I could simply use:

MAXX( ALLSELECTED ( Table/Column ) , [Measure] )

However, both Table/Column  AND Measure need to be dynamic.
I've gotten the Measure part to work with a SWITCH(TRUE(), however I cannot dynamically select a column/table within the ALLSELECTED part.

Is there any way to dynamically reference a different column/table, based on a parameter selection, within the ALLSELECT statement?

I would like to hear from you.

Kind regards,

Daniël

[DataNinja777 · Accepted solution]

Hi @Foxxon28 ,

To create a dynamic MAXX measure in Power BI that adjusts based on both the X-axis (dimension) and Y-axis (measure) selections, you can achieve this by leveraging field parameters, SWITCH statements, and DAX functions like SUMMARIZE and ALLSELECTED. The challenge here is that DAX doesn’t natively support fully dynamic column or table references inside functions like ALLSELECTED. However, with a combination of techniques, you can accomplish this dynamically.

Start by creating two field parameters. The first field parameter will control the X-axis, allowing users to switch between columns like Location, ProductType, and Salesperson. The second field parameter will control the Y-axis, allowing users to switch between different measures, such as [Measure 1] and [Measure 2]. You can create these field parameters using the following DAX syntax:

X Axis Parameter = {
    ("Location", 'Table'[Location], 0),
    ("Product Type", 'Table'[ProductType], 1),
    ("Salesperson", 'Table'[Salesperson], 2)
}

Measure Parameter = {
    ("Measure 1", [Measure 1], 0),
    ("Measure 2", [Measure 2], 1)
}

Once the field parameters are created, you can move on to writing the dynamic MAXX measure. This measure will adjust based on the selected parameter values and calculate the maximum value across the selected dimension and measure. The measure starts by retrieving the selected X-axis column and measure using SELECTEDVALUE. Then, it uses a SWITCH statement to handle the dynamic selection of columns within the ALLSELECTED function. Here’s the DAX code for the Dynamic MAXX measure:

Dynamic MAXX =
VAR SelectedAxis = SELECTEDVALUE('X Axis Parameter'[X Axis Parameter])
VAR SelectedMeasure = SELECTEDVALUE('Measure Parameter'[Measure Parameter])

RETURN
SWITCH(
    TRUE(),
    SelectedAxis = "Location", MAXX(ALLSELECTED('Table'[Location]), SelectedMeasure),
    SelectedAxis = "Product Type", MAXX(ALLSELECTED('Table'[ProductType]), SelectedMeasure),
    SelectedAxis = "Salesperson", MAXX(ALLSELECTED('Table'[Salesperson]), SelectedMeasure),
    BLANK()
)

In this measure, the logic ensures that the maximum value is calculated dynamically for the selected column and measure. However, handling dynamic measures requires further adjustments. To reference the measure dynamically, use another SWITCH statement within the measure logic. Here’s the code to handle the dynamic measure selection:

SelectedMeasure =
SWITCH(
    TRUE(),
    'Measure Parameter'[Measure Parameter] = "Measure 1", [Measure 1],
    'Measure Parameter'[Measure Parameter] = "Measure 2", [Measure 2],
    BLANK()
)

If you need to calculate the max across different dimensions dynamically, you can use the SUMMARIZE function to create a temporary table that combines the selected dimension and measure. This approach allows you to calculate the maximum value across the summarized table. Here’s how you can do that:

Dynamic MAXX =
VAR TempTable = 
    SUMMARIZE(
        'Table',
        SELECTEDVALUE('X Axis Parameter'[X Axis Parameter]),
        "MeasureValue", [SelectedMeasure]
    )
RETURN
MAXX(TempTable, [MeasureValue])

Finally, use the Dynamic MAXX measure in your column chart to dynamically adjust the Y-axis based on the parameter selections. By following this approach, you can achieve a fully dynamic column and measure selection in Power BI visuals, ensuring the chart reflects the maximum value dynamically based on user input.

Best regards,