Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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 =
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.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
6 | |
5 |
User | Count |
---|---|
20 | |
14 | |
10 | |
9 | |
6 |