Forum Discussion
Filtering out zero/blank Matrix rows, part XXXVI
Hi all -- I have a variant of the old question "how do I filter out zero rows in a matrix visual" that I can't work out. Surely it has been addressed in here somewhere, but I've not turned up the right search criteria to reveal the answer to me. So, here goes.
Let's say I have this matrix:
The numbers here are a single measure, Inventory, sliced by item and month dimensions.
So now I want to filter out 0 (and blank) rows. The standard approach would be to make a new measure, like:
InventoryFilter = IF(SUM(Inventory) <> 0, 1, 0)
And then use this as a filter on the matrix, filtering for values of only 1.
This will work fine to filter out the Pencils row, which totals 0 but also has a 0.
But it will also filter out the Pens row, which totals 0 but has individual values I don't want to have filtered out. They just happen to sum to 0.
Since the matrix values are a single measure sliced by a dimension, rather than three (in this example) mesures, my flltering measure can't check each column individually for 0s e.g. InventoryFilter = IF(SUM(InventoryCol1) <> 0 && IF(SUM(InventoryCol2) <> 0 && IF(SUM(InventoryCol3) <> 0, 1, 0).
So how would I instead construct a filter measure that checks each dimensional slice of the measure on the row for 0 (or blank) so I can then filter only rows where all values are 0 or blank, and not rows (like Pens in this example) that have values that happen to total to 0?
Or is there another way to do this? Many thanks to you all.
Hi markmsc,
You can try below measure and use it as a filter on table visual
Inventory Row Filter =
VAR NonZeroMonths =
COUNTROWS(
FILTER(
ALLSELECTED('Date'[Month]), -- your column field (May, June, July)
NOT ISBLANK(CALCULATE([Inventory])) &&
CALCULATE([Inventory]) <> 0
)
)
RETURN
IF(NonZeroMonths > 0, 1, 0)🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
10 Replies
Hi markmsc,
You can try below measure and use it as a filter on table visual
Inventory Row Filter =
VAR NonZeroMonths =
COUNTROWS(
FILTER(
ALLSELECTED('Date'[Month]), -- your column field (May, June, July)
NOT ISBLANK(CALCULATE([Inventory])) &&
CALCULATE([Inventory]) <> 0
)
)
RETURN
IF(NonZeroMonths > 0, 1, 0)🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Hello grazitti_sapna -- thank you for your reply. This worked for me! With one modification.
Turns out my example case was too simplified. Lets assume the units of pencils, pens, and markers is boxes. e.g. May inventory of pens as shown in my post is 100 boxes. That 100 boxes is comprised of smaller shipments, say a shipment of 10 boxes, one of 20 boxes, etc. The total of all shipments equalling 100 boxes.
Further, the underlying unit of measure in the system is actual units, like the actual number of pens. Reporting that unit makes for numbers that are too large, so the busienss prefers the aggregrate unit of measure boxes. Let's say each box contains 12 pens. So if I have 24 pens, I have two boxes. But if I have 9 pens, I have 0.75 boxes.
They want everything reported in the nearest whole box. So in May if I have shipments of 80 pens, 37 pens, 25 pens, etc., and I am reporting 100 boxes, I probably don't have exactly 1200 pens. I might have 1197 or 1208 or someting that, when divided by 12 and rounded to the nearest integer, is 100.
All of that is to say that your approach did not work for me at first, because even though I have plenty of 0s in the matrix, very few of them are actually exactly 0, which your measure is looking for. So after seeing that, I made this one change to one line:
CALCULATE(ROUND([Inventory]),0) <> 0And then it worked! This was great. Overall it was the FILTER ALLSELECTED on the month dimension that was the key I was missing, and then just tuning your example for my actual numbers as described here. Thank you very much.
Hi markmsc
I would normally suggest creating a measure (or an equivalent calculation item) that converts unwanted values to blank, and rely on the default Power BI behaviour where completely blank rows or columns are hidden (due to
SUMMARIZECOLUMNS).However I'm not certain that would meet your requirements.
To clarify, assume this is the original matrix with an additional item "Erasers" and a Total column:
Would you be happy converting the above to either of these options, or something else?
Option A (all zeros converted to blanks):
Option B (zeros converted to blanks only where entire row is zero/blank):
Measure:
Has Non Zero =
SUMX(
VALUES('Month'[Month]),
IF(ISBLANK([Inventory]), 0, IF([Inventory] <> 0, 1, 0))
) > 0Filter: Visual level → Has Non Zero = 1
Hi Kedar_Pande -- thanks for your reply. This solution did not work for me unfortunately, possibly in part due to an issue I described in my reply to grazitti_sapna in this thread. Please see the reply for the solution I went with. Thank you again for your help. Really appreciate it.
Hi markmsc
Power BI automatically removes a completely blank row so return a zero as blank.
IF ( [this measure] = 0, BLANK (), [this measure] )Alternatively, you can remove zero directly from the main measure itself
CALCULATE ( SUM ( 'table'[column] ), KEEPFILTERS ( 'table'[column] <> 0 ) )Hi danextian -- thanks for your reply. This solution did not work for me unfortunately, possibly in part due to an issue I described in my reply to grazitti_sapna in this thread. Please see that reply for the solution I went with. Thank you again for your help. Really appreciate it.
Please try the measure below as yout visual filter:
InventoryFilter = VAR _MaxAbsValue = MAXX ( VALUES ( 'Date'[Month] ), ABS ( [Inventory] ) ) RETURN IF ( _MaxAbsValue > 0, 1, 0 )Hi cengizhanarslan -- thanks for your reply. This solution did not work for me unfortunately, possibly in part due to an issue I described in my reply to @grazitti_sapna in this thread. Please see that reply for the solution I went with. Thank you again for your help. Really appreciate it.
