This video shows how to use DAX and conditional formatting together to highlight the minimum and maximum values in a matrix in Power BI. Article and download: sql.bi/662096?aff=yt
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.
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? :)
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...
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.
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/
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 :)
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.
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/
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/
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?
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".
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
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?
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
Change the ALLSELECTED condition to only include the items you want. For example, use ALLSELECTED ( 'Product'[Brand] ) instead of ALLSELECTED() in the sample file.
@@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)
@@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
@@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
