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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
gap
Helper I
Helper I

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

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)
 
gap_0-1680020989900.png

 

 gap_1-1680021298522.png

 

7 REPLIES 7
wdx223_Daniel
Super User
Super User

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

then, select those rows get 0

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.

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

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?

i do not understand too.😓

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

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

Helpful resources

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

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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