cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors