cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

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

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 (

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 (

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?

2 ACCEPTED SOLUTIONS
Super User

@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(
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] )

Anonymous
Not applicable

It worked JD, thanks!!!

3 REPLIES 3
Super User

@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(
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] )

Frequent Visitor

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
Not applicable

It worked JD, thanks!!!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors