topic Matrix - filter every red coloured rows in DAX Commands and Tips

Matrix - filter every red coloured rows

JFR2022 — Thu, 12 Oct 2023 12:19:31 GMT

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.

Re: Matrix - filter every red coloured rows

PurpleGate — Fri, 13 Oct 2023 07:45:47 GMT

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

Re: Matrix - filter every red coloured rows

Anonymous — Mon, 16 Oct 2023 05:04:58 GMT

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

Re: Matrix - filter every red coloured rows

JFR2022 — Mon, 16 Oct 2023 11:59:12 GMT

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:

Re: Matrix - filter every red coloured rows

JFR2022 — Mon, 16 Oct 2023 13:15:58 GMT

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.