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 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:

 

 

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

 

 

 

 

4 Replies

  • PurpleGate's avatar
    PurpleGate
    Icon for Resolver III rankResolver III
    2 years ago

    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

     

     

    • JFR2022's avatar
      JFR2022
      Frequent Visitor
      2 years ago

      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:

       



       
  • 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.