Forum Discussion

JFR2022's avatar
JFR2022
Frequent Visitor
2 years ago

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 m...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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)

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

3. Result:

 

 

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

JFR2022's avatar
JFR2022
Frequent Visitor
2 years ago

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.