Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JFR2022
Frequent Visitor

Matrix - filter every red coloured rows

Hi,

 

For a stock overview report i've created a conditonal measure which colours the value for each article in each month red if the stock goes below zero or below the minimal stock quantity. To make it more user friendly for the user I would like to have a filter that only shows every red coloured cell in the matrix instead of the whole list.

 

The filter I've made is unfortunately only looking to the stock value of the last month (2023-11) in the matrix, so if there is a stock issue in 2023-10 but in 2023-11 it is resolved, this whole row is filtered out. How can I fix this so I always see all the red rows?

 

Thanks in advance!

 

Measure I'm using is:

Kleurfilter = IF([kleurvariabelen 1] = "#EFB5B9", 1,0)
[Kleurvariabelen 1] is a measure for determining if the value should be red or transparent.
 

This is how the filter works at the moment:

JFR2022_2-1697112987733.png

 

 

If the filter is off you can see that the outlined row is missing while the value is red in 2023-10.

JFR2022_3-1697113063203.png

 

 

 

 

4 REPLIES 4
Anonymous
Not applicable

Hi  @JFR2022 ,

 

Here are the steps you can follow:

1. Create measure.

Kleurfilter =
var _min=MINX(FILTER(ALL('True'),'True'[Attribute]=MAX('True'[Attribute])),'True'[Value])
return
IF(
    MAX('True'[Value]) <0 || MAX('True'[Value])< _min,"white","red")
Flag =
var _count=
COUNTX(
    FILTER(ALL('True'),
    [Kleurfilter]="red"&&'True'[Artikelnr]=MAX('True'[Artikelnr])),[Kleurfilter])
return
IF(
    _count >=1,1,0)

vyangliumsft_0-1697432635681.png

2. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_1-1697432635683.png

3. Result:

vyangliumsft_2-1697432675078.png

 

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi,

 

Thank you for your reply!

I've downloaded your .pbix file but I think my situation is a little bit different. Right now your suggestion for the "kleurfilter" is not working. The calculation of the stock is based on a measure 'cumulative voorraad" (cumulative stock), so the MAX function in your measure is not working for me because I cant use a column. I'm using the YearMonthNumber from a Date table "Date toekomst" 
The kleurfilter measure if tried for your solution:

Kleurfilter1 = 
var _min=MINX(FILTER(ALL('Date toekomst'),'Date toekomst'[YearMonthnumber]=MAX('Date toekomst'[YearMonthnumber])),[cumulatieve voorraad])
return
IF(
    [cumulatieve voorraad] < 0 || [cumulatieve voorraad] < _min,"#EFB5B9","#FFFFFF")

 

The conditional for the cumulative stock to turn red = 

var _kleurvariabelen = if(OR([cumulatieve voorraad] < [som minimale voorraad], [cumulatieve voorraad] < 0),"#EFB5B9","#FFFFFF"
[som minimale voorraad] = minimum stock.
 
PurpleGate
Resolver III
Resolver III

Could you try adding some variables to the measure so that it looks at each column individually? 

 

var _thismonth =  CALCULATE(SELECTEDVALUE(Date[Month]),FILTER(Date,Date[Date] = TODAY()))
var _lastmonth = CALCULATE(SELECTEDVALUE(Date[Month]),DATESMTD(DATEADD(Date[Date],-1,MONTH)))
var _filter =IF(OR(AND([kleurvariabelen 1] = "#EFB5B9",lastmonth ),AND([kleurvariabelen 1] = "#EFB5B9",thismonth)), 1,0)
Return _filter

 

 

Hi, 

Thank you for your reply!

Unfortunately this is not working for me at the moment. This is how my measure looks right now, to make it more clear i've added the "kleurvariabelen" measure as a variable to the new measure. It basically says if the cumulative stock measure > the minimum stock measure or >0 it should become red.

I've adjusted your _lastmonth to _nextmonth because I have to look in te future instead of the past.

 

Kleurfilter = 
var _kleurvariabelen = if(OR([cumulatieve voorraad] < [som minimale voorraad], [cumulatieve voorraad] < 0),"#EFB5B9","#FFFFFF")
var _thismonth = calculate(SELECTEDVALUE('Date toekomst'[YearMonthnumber]),filter('Date toekomst','Date toekomst'[Date] = today()))
var _nextmonth = calculate(SELECTEDVALUE('Date toekomst'[YearMonthnumber]),DATESMTD(DATEADD('Date toekomst'[Date],1,MONTH)))
var _filter = IF(OR(AND(_kleurvariabelen = "#EFB5B9",_thismonth),and(_kleurvariabelen = "#EFB5B9",_nextmonth)),1,0)
Return _filter

 

The result of this measure is the same as I've had with my original measure. The YearMonthNumber is the column I'm using in my slicer list:

JFR2022_0-1697457447961.png

 



 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.