topic Re: Filter Matrix Rows to be visible only when all columns have values in DAX Commands and Tips

Filter Matrix Rows to be visible only when all columns have values

gap — Tue, 28 Mar 2023 16:35:54 GMT

Hello

My goal is to hide rows on a matrix when not all columns have value. So i created the below filter. What i am counting is the the total columns on each row (columns will be the Year), where a specific measure has value. Then i am comparing this value with the total Years (so total columns), returning 1 if this is the same value. Then i will filter 1 on the Matrix. But this is not working 😞

When i am returning the _nonblankyears, then the value is the expected one as you can see on the first screenshot. When there is a missing value, then the Basket Quantities Filter returns for example 4 instead of 5 (5 are the selected years). When i filter the visual and enter the value 5, then the matrix is filtered correctly. Of course the _years also returns correct values (in this case 5 for all the columns).

But when i am using the 1,0 values to make the filter work more dynamicaly, then is not working anymore, and i am really struggling to figure out why.....

Basket Quantities Filter =

var _years = COUNTROWS(ALLSELECTED('Date'[Year]))

var _nonblankyears =

COUNTROWS(

FILTER(

CROSSJOIN(

VALUES(Items[Item Name]),

ALLSELECTED('Date'[Year])

NOT(ISBLANK([Basket Quantities Price]))

)

RETURN

IF(_nonblankyears = _years, 1)

Re: Filter Matrix Rows to be visible only when all columns have values

wdx223_Daniel — Wed, 29 Mar 2023 07:24:25 GMT

Filter=COUNTROWS(FILTER(ALLSELECTED('Date'[Year]),ISBLANK([Basket Quantities Price]))

then, select those rows get 0

Re: Filter Matrix Rows to be visible only when all columns have values

gap — Wed, 29 Mar 2023 07:50:05 GMT

Thanks. The problem is that it will filter the results only when the Filter measure is added to the values. Then i remove it (but of course keep it on the filters), then all the rows returned.

Re: Filter Matrix Rows to be visible only when all columns have values

wdx223_Daniel — Thu, 30 Mar 2023 00:10:33 GMT

Filter=IF(ISBLANK([Basket Quantities Price]),1)

Re: Filter Matrix Rows to be visible only when all columns have values

gap — Thu, 30 Mar 2023 07:32:43 GMT

This will not work, since it will not return same value for all the columns on the rows i want to keep, therefore the filtering will not be correct

Re: Filter Matrix Rows to be visible only when all columns have values

gap — Thu, 30 Mar 2023 07:48:38 GMT

What i really dont understand is why if i use filter=5 (the first screenshot) it works even if i remove the column from the table, but on the second case (or on the solution you provided) is not working. Since tha values are as it should be, why on some cases the table is filtered correctly and on the others not?

Re: Filter Matrix Rows to be visible only when all columns have values

wdx223_Daniel — Thu, 30 Mar 2023 08:24:27 GMT

i do not understand too.😓

Re: Filter Matrix Rows to be visible only when all columns have values

gap — Thu, 30 Mar 2023 08:54:05 GMT

Found the below which heps understanding the issue. Generally adding a +0 at the measure in order not to be blank, solves the filtering issue. I just need to find a solution with no adding the +0, but at least that article helps understanding the cause.

Deep dive into measure filters in Power BI Desktop - SQLBI