Max of a parametric measure based on a parametric dimension; both controlled by slicers

[lauriemclolo]

Hello.  I have a parmeter as my y (measure) and a parameter as my x (dim), both controlled by slicers for my column chart.  I have been asked to add a SEPARATE HIGHLIGHT visual to my page which displays the MAX of the Y based on the Choice of both X and Y in the slicer.  In the pic below, when MEMBER TYPE is chosen as the dimension, the max for the member_type falls in the GoldPlat category of member_type.  So, I want a HIGHLIGHT visual that says "For Member Type the max  Total Rewards Issued  is in Gold Platinum Member_type for 115M rewards issued".  If the user switches the x or y slicer choices, I want the max to recalulated based on the measure chosen and the Dimension to change based on the x dimension choice.  Hope this makes sense.

[danextian · Accepted solution]

You will need to create a measure that checks what measure is selected and what field it is to be evaluated against.

-- Capture the selected measure parameter (e.g., 1 = MeasureParam1, 2 = MeasureParam2)
VAR _MeasureParam = SELECTEDVALUE(MeasureParam[MeasureParam Order])

-- Capture the selected dimension parameter (e.g., 1 = DimParam1, 2 = DimParam2, 3 = DimParam3)
VAR _DimParam = SELECTEDVALUE(DimParam[DimParam Order])

RETURN
SWITCH(
    _MeasureParam,  -- Choose logic based on which measure parameter is selected

    1,  -- If MeasureParam1 is selected
        SWITCH(
            _DimParam,  -- Then choose which dimension to calculate against
            1, MAXX(ALLSELECTED('table'[DimParam1 Column]), [MeasureParam1 Measure]), -- DimParam1
            2, MAXX(ALLSELECTED('table'[DimParam2 Column]), [MeasureParam1 Measure]), -- DimParam2
            3, MAXX(ALLSELECTED('table'[DimParam3 Column]), [MeasureParam1 Measure])  -- DimParam3
        ),

    2,  -- If MeasureParam2 is selected
        SWITCH(
            _DimParam,  -- Then choose which dimension to calculate against
            1, MAXX(ALLSELECTED('table'[DimParam1 Column]), [MeasureParam2 Measure]), -- DimParam1
            2, MAXX(ALLSELECTED('table'[DimParam2 Column]), [MeasureParam2 Measure]), -- DimParam2
            3, MAXX(ALLSELECTED('table'[DimParam3 Column]), [MeasureParam2 Measure])  -- DimParam3
        )
)

[lauriemclolo]

@danextian  In the switch statement am I using the results of the chosen dimension in the dimension parameter to point back to the table that contains the dimensions themeselves (not the parameter)?  In other words, is "'table'[DimParam1 Column]" in your example the actual table and field associated with the 1st row of the dimension parameter, ?  It's not erroring out but the resultant value it's displaying is not correct.  I will work on it and get back to you again.

[v-veshwara-msft]

Hi @lauriemclolo ,

Thanks for posting in Microsoft Fabric Community.

Just checking in to see how things are going, and thanks again to @danextian for sharing the approach earlier.

As mentioned, in the SWITCH statement the 'table'[DimParam1 Column] reference should point to the actual column in your model that corresponds to the selected dimension parameter, not the parameter table itself. The parameter only determines which column is evaluated. Using ALLSELECTED or VALUES on that column should help get the correct result.

Have you been able to make any progress with this?

Please reach out for further assistance.

Thank you.

[Shahid12523]

You want a highlight card that dynamically shows:

Which dimension category (X slicer) has the max value

For whichever measure (Y slicer) is selected

With the actual max number

Since DAX can’t make the dimension fully dynamic, you build separate measures per dimension and switch them based on the slicer.

DAX

1. Selected Measure (Y slicer):

Selected Measure =
SWITCH(
SELECTEDVALUE(MeasureParam[Measure]),
"Total Rewards Issued", [Total Rewards Issued],
"Transactions", [Transactions],
"Profit", [Profit]
)

2. Max value per dimension (example: Member Type):

Max by Member Type =
MAXX(
VALUES('Table'[Member Type]),
[Selected Measure]
)

3. Category with max:

Max Member Type =
MAXX(
TOPN(
1,
ADDCOLUMNS(
VALUES('Table'[Member Type]),
"@val", [Selected Measure]
),
[@val], DESC
),
'Table'[Member Type]
)

4. Final highlight text:

Highlight =
" For " & SELECTEDVALUE(DimensionParam[Dimension]) &
" the max " & SELECTEDVALUE(MeasureParam[Measure]) &
" is in " & [Max Member Type] &
" for " & FORMAT([Max by Member Type], "#,##0")

✅ This will auto-update when the user changes either slicer.

[GeraldGEmerick]

@lauriemclolo You would need to do something like the following although it is difficult to be specific because there is not much to go on here.

Max = 
    VAR _x = MAX( 'x'[x] )
    VAR _y = MAX( 'y'[y] )
    VAR _Colors = SUMMARIZE( 'Table2', [Color], "Value 1", SUM( 'Table2'[Value 1] ), "Value 2", SUM( 'Table2'[Value 2] ) )
    VAR _MemberTypes = SUMMARIZE( 'Table2', [Member Type], "Value 1", SUM( 'Table2'[Value 1] ), "Value 2", SUM( 'Table2'[Value 2] ) )
    VAR _Result = 
        SWITCH(
            _x,
            "Color", IF( _y = "Value 1", MAXX( _Colors, [Value 1] ), MAXX( _Colors, [Value 2] ) ),
            "Member Type", IF( _y = "Value 1", MAXX( _MemberTypes, [Value 1] ), MAXX( _MemberTypes, [Value 2] ) )
        )
RETURN
    _Result

[lauriemclolo]

I'm sorry, thank you but I don't understand this  I'm not asking for help with colors or for a sum. I'm just asking for help with the max of a parameterized measure based on a parameterized dimension.  Thanks.