Highlighting the minimum and maximum values in a Power BI matrix

[@WesleyYappy-n1m]

2:16 `=ALLSELECTED` is used to restore the filter context for the current visual while excluding other filters. The filter context in this case is based on the calendar year, and we want to calculate the `MINX` and `MAXX` values for the year currently visible, rather than for all three years.

[@hcedenop]

Nice and clean solution... not a heat map, but a simpler eye catching solution without having user to find out which green is greener...

[@mehdihammadi6145]

Thank you 💖 for sharing *Alberto* . I just used it in one of my reports and it worked as a charm.

[@aleksandarboshevski]

Great job @AlbertoFerrari, please continue with producing useful content like this.

[@Ad4lon86]

Thank you Alberto for this and other videos, amazing content as always. I am just going through your DAX book and I'm learning at the speed of light! Much appreciated.

[@kkskhin7208]

Smart Explanation as always, thank you so much for your valuable sharing.

[@bentatlim]

Thank you very much for your amazing explanation 👍👍👍

[@JoseMorales-um8qw]

Thank you for sharing Alberto, this is a brilliant idea!

[@leonardomendizabal1966]

Excellent !!! Greetings from Mexico ! :)

[@ricardopinxt212]

Superb as ever Alberto! 👍 Small side step. How would the code change as you want the min and max only for every column en not the entire matrix? So the Max/Min in Asia, Europe and North America.

[@nikhil1888]

Did you find the solution for this?

[@SQLBI]

Use ALLSELECTED( Product ) instead of ALLSELECTED(). See the notes at the end of the article at www.sqlbi.com/articles/highlighting-the-minimum-and-maximum-values-in-a-power-bi-matrix/

[@josuevergara218]

Alberto a question, if he wanted to add the sales of only those two products the minimum and the maximum as that scenario could be solved ?

[@wasalanisimudiyanse981]

Hi Alberto, Thank you for the video. If I need to highlight the cell range ( Ex- Call Start time to end time) What would be the best solution?

[@talytarosas9711]

Very useful. Thank you so much

[@MikheiliGubelidze]

hello is it possible to formatting certain Row field in Matrix Visuals (For example : if i have category and product fields and i want to format just food category Row )

[@ajaysharma2061]

Very helpful. Thank you so much.

[@mshparber]

Brilliant! Thank you! What if I have another column nested after Brand and I want to dynamically calculate depending if it is drilled... The SUMMARIZE is static...

[@albertoferrari6893]

It is, but by using ISINSCOPE you can compute the value in different ways depending on the level you are in.

[@joaoformigo1764]

Hello alberto! thank you for the vid amazing as always! What if I want to use this highlight of max and min on a column chart and I want it to work while i drilldown into it? If for example i have years since 1950-2020 and I have it displayed by bins of 10 years each. I managed to get the bins to highlight and the years aswell if i drilldown to the last level of the hierarchy but If I drilldown to the bin for example 1960 (that is 1960-1969) it doesn't highlight the highest and lowest in that drilldown Any tip? :)

[@michaelmays9415]

Great video! Why do you need MAXX and MINX instead of MAX and MIN? Would either work?

[@SQLBI]

Yes both work in this case.

[@santoshf]

Need Help!! I am trying to get max and min from each country any hints would be appreciated. Thank you.

[@luisfelipecampos4776]

Thanks a lot master Ferrari.

[@mshparber]

A question: I am summarizing (MINX, MAXX) BALANCES measure, not sales transactions amount. So, SUMMARIZE(Sales, 'Calendar'[Year-Month], 'Customers'[Customer]....) - misses the cases where in a certain month a customer didn't have a sale, but does have a Balance.... I feel I need to somehow create a CROSSJOIN between Year-Month, Customer and Product, but I am afraid of CROSSJOINS... Any suggestions?

[@mshparber]

I finally tried: CALCULATETABLE(ADDCOLUMNS(CROSSJOIN(VALUES('Calendar'[Year-Month]), VALUES('Customers'[Customer])...),"@Bal",[Balance), my_filters)... It is working, but I wonder if this is the optimal solution: CALCULATETABLE + ADDCOULUMNS + CROSSJOIN (VALUES (TableA[Column1]), VALUES (TableB[Column2]),...)...)...)? @SQLBI

[@sbn4862]

My PowerBI desctop Free version updated and i can't fined Condtional formating.Where removed this optional?

[@arunkumarl5747]

Hello Sir, Can we also find max and min values from 2 different tables?

[@mohdmuzammil3059]

Very useful what if i want to highlight min & max value for each respective continent columns pls respond

[@nikhil1888]

Did you find the solution for this?

[@SQLBI]

Use ALLSELECTED( Product ) instead of ALLSELECTED(). See the notes at the end of the article at www.sqlbi.com/articles/highlighting-the-minimum-and-maximum-values-in-a-power-bi-matrix/

[@gugoanx]

Amazing! Help me a lot

[@usmanasad180]

Thanks, boss, its help a lot

[@vinayanandks]

Hi, How to get get min and max values in a month into a separate column and also its respective dates, if min and max values are same then date gives a conflict, how to make it take the latest date, could you pls help with formula

[@brunonard25]

Hello Alberto, do you have a solution to highlight the max value for each respective continent column?

[@nikhil1888]

Did you find the solution for this?

[@SQLBI]

Use ALLSELECTED( Product ) instead of ALLSELECTED(). See the notes at the end of the article at www.sqlbi.com/articles/highlighting-the-minimum-and-maximum-values-in-a-power-bi-matrix/

[@feo786]

Nice. Is it possible to perhaps apply something similar to a line chart where you want to make the minimum and maximum values stand out?

[@albertoferrari6893]

You need conditional formatting to control the visual, AFAIK you don't have such a property in charts... at least, as of now.

[@martinlivingstone5824]

Thank you Alberto

[@AishaMumin-fr5bo]

You just saved me.

[@AutodafeSynths]

I was trying to do something similar, but I would need to highlight min and max for each single row of the matrix...

[@SQLBI]

Change the ALLSELECTED condition to only include the items you want. For example, use ALLSELECTED ( 'Product'[Brand] ) instead of ALLSELECTED() in the sample file.

[@AutodafeSynths]

@@SQLBI Thanks a lot, looks like it worked, even if my report is a bit more complicated, I have a matrix with a "dynamic" measure that calculates costs in Euros or Euros/Hectares for different Cultivations and Operations (I am dealing with a large farming company)

[@kerkoules]

@@AutodafeSynths @SQLBI I did replace ALLSELECTED () with ALLSELECTED ( 'Product'[Brand] ), but the result is not the expected one. All three columns (Asia+Europe+NA) are getting colored across the Product rows (Contoso +Tailspin Toys). What I am after is to treat each row independently, coloring the min and max values of each row and not the min and max of the entire matrix. Could you kindly advise? Thank you

[@kerkoules]

@@SQLBI I did replace ALLSELECTED () with ALLSELECTED ( 'Product'[Brand] ), but the result is not the expected one. All three columns (Asia+Europe+NA) are getting colored across the Product rows (Contoso +Tailspin Toys). What I am after is to treat each row independently, coloring the min and max values of each row and not the min and max of the entire matrix. Could you kindly advise? Thank you

[@nikhil1888]

@@kerkoules did you find the solution of this?

[@anjaliprabhu6696]

Hi, Your video content and quality is great compare to Pettaka Technologies Power BI videos.

[@arttube13]

Thank you for this interesting method, I am having trouble applying it to my scenario, which is as the following: My matrix is created using two columns from two different tables and the value is a measure, another issue is that I can't use "ALLSELECTED" since my report is filtered to show the last 30 days. Can you assist me :)

[@SQLBI]

For these questions look at the comments area in the related article, there are more people who could answer. However, ALLSELECTED should work in your scenario.

[@aungkolatt6586]

Thanks a lot Sir!

[@huangdanny250]

Why need to use the ALLselected Function in the Calculatable Function ?

[@SQLBI]

Performance. This way the table variable is computed only once for all the cells of the visual, resulting in faster query execution time.

[@huangdanny250]

@@SQLBI Thanks for answering!!! so Allselected() is like fixing the table not to be filter to one cells ?

[@Kmsbi]

useful information

[@jovianosilveira9357]

Simples e Fácil. Assim que tem que ser!

[@rafaelbarbosa169]

I select 2 rules in conditional is Igual 0 percent to find minimum value and select 100 percent to find the maximum value, I think this same solution, my question: What solution is more performatic?

[@SQLBI]

The result is identical for this specific case. The technique shown in this video can be applied to more complex business logic, the DAX code of the measure that controls the highlight can be customized - for example, it could be based on the distribution of underlying data, like "highlight cells that have one single product making more than 50% of the entire brand".

[@swarajyalakshmi8017]

Thank you

[@hamidsh4789]

unfortunately for me, instead of SUM measure used COUNT measure but didn`t work. Anyway thanks for the video.

[@hamidsh4789]

I solved it. My problem was that instead of using COUNT measure I used COUNT directly in your mentioned code and it did not work. Thank you so much.

[@sergiocaptuleio1088]

Show!!!!