Highlighting the max and min values of a row in a matrix

[Anonymous]

Hi everyone!

I watched one of Albertro Ferrari's videos in which he highlights the maximum and minimum values in a matrix.

I am trying to tweek his pattern in order to highlight the maximum and minimum for each line of the matrix.  Alberto’s measure, which works perfectly, goes like this:

Show Extremes =

VAR ValuesDisplayed =

        CALCULATETABLE (

            ADDCOLUMNS (

             SUMMARIZE ( Invoices; DimSector[cdSector];  DimGroup[nmGroup]  );

             "@AMT"; [Fees]

            );

            ALLSELECTED()

        )

VAR MinVal = MINX ( ValuesDisplayed; [@Amt] )

VAR MaxVal = MAXX ( ValuesDisplayed; [@Amt] )

VAR CurrentVal = [Fees]

VAR Result =

    SWITCH(

        TRUE();

        CurrentVal = MinVal; 1;

        CurrentVal = MaxVal; 2

    )

RETURN

    Result

I have changed this by replacing the ALLSELECTED() by another filter, specifically:

DimGroup[Group]  = SelGroup

and, I added a line with

VAR SelGroup = SELECTED VALUE (DimGroup[nmGroup])

at the very beginning.

After the changes the measure is now like this:

Show Extremes =
VAR SelGroup = SELECTED VALUE (DimGroup[nmGroup])

VAR ValuesDisplayed =

        CALCULATETABLE (

            ADDCOLUMNS (

             SUMMARIZE ( Invoices; DimSector[cdSector];  DimGroup[nmGroup]  );

             "@AMT"; [Fees]

            );

            DimGroup[Group]  = SelGroup

        )

VAR MinVal = MINX ( ValuesDisplayed; [@Amt] )

VAR MaxVal = MAXX ( ValuesDisplayed; [@Amt] )

VAR CurrentVal = [Fees]

VAR Result =

    SWITCH(

        TRUE();

        CurrentVal = MinVal; 1;

        CurrentVal = MaxVal; 2

    )

RETURN

    Result

After these changes, the first resulting table ‘ValuesDisplayed’ is correct. It returns only the lines for the selected group. I tested this using Dax Studio.

However, the MinVal and MaxVal vars seem to be seeing only the current line of ValuesDisplayed, and this results in that MinVal and MaxVal are always equal to CurrentVal.

I just can’t discover what the problem is here.  Anyone can see my mistake?

[jdbuchanan71 · Accepted solution]

@Anonymous 

I downloaded Alberto's file to mess with.  By changing the SUMMARIZE to look at only continet and the ALLSELECTED to do the same I was able to get the example to hightlight the MIN and MAX on each row.

MinMax = 
VAR Vals = 
    CALCULATETABLE(
        ADDCOLUMNS (
            SUMMARIZE ( Sales, Store[Continent] ),
            "@SalesAmt", [Sales Amount]
        ),
        ALLSELECTED ( Store[Continent] )
    )
VAR MinValue = MINX ( Vals, [@SalesAmt] )
VAR MaxValue = MAXX ( Vals, [@SalesAmt] )
VAR CurrentValue = [Sales Amount]
VAR Result = 
    SWITCH ( 
        TRUE,
        CurrentValue = MinValue, 1, -- 1 for MIN
        CurrentValue = MaxValue, 2  -- 2 for MAX
    )
RETURN
    Result

SUMMARIZE ( Sales, Store[Continent], 'Product'[Brand] ) change to SUMMARIZE ( Sales, Store[Continent] )
ALLSELECTED () changed to ALLSELECTED ( Store[Continent] )

[jdbuchanan71 · Accepted solution]

@Anonymous 

I downloaded Alberto's file to mess with.  By changing the SUMMARIZE to look at only continet and the ALLSELECTED to do the same I was able to get the example to hightlight the MIN and MAX on each row.

MinMax = 
VAR Vals = 
    CALCULATETABLE(
        ADDCOLUMNS (
            SUMMARIZE ( Sales, Store[Continent] ),
            "@SalesAmt", [Sales Amount]
        ),
        ALLSELECTED ( Store[Continent] )
    )
VAR MinValue = MINX ( Vals, [@SalesAmt] )
VAR MaxValue = MAXX ( Vals, [@SalesAmt] )
VAR CurrentValue = [Sales Amount]
VAR Result = 
    SWITCH ( 
        TRUE,
        CurrentValue = MinValue, 1, -- 1 for MIN
        CurrentValue = MaxValue, 2  -- 2 for MAX
    )
RETURN
    Result

SUMMARIZE ( Sales, Store[Continent], 'Product'[Brand] ) change to SUMMARIZE ( Sales, Store[Continent] )
ALLSELECTED () changed to ALLSELECTED ( Store[Continent] )

[iamafanijaz]

Hi jd, what if we have multiple dimensions on rows. using the above example, we have let say Brand and Category in hierarchy on rows. so in that case how we can find the min, max value? adding on it, we want to highligh the min, max on row total as well

[Anonymous · Accepted solution]

It worked JD, thanks!!!