Forum Discussion
Suppressing "Blank" Rows (formatting issue?)
I have a requirement where the users don’t want to see rows where there are no values displayed in any columns in a Matrix visualization. I believe PBI automatically does this (unless you ask it not to). However, I think the issue I'm having is related to using the format expression “ #,#,;(#,#,);” to display values in the thousands by dropping the last three numbers (e.g. 2,000,000,000 becomes 2,000,000). It looks to me like Power BI is considering any value between -999 and 999 to be non-blank/empty and therefore displays any rows with underlying values between -999 and 999 even though there’s nothing to display once the number is formatted into thousands using the above format string. Additionally, numbers between -1and -999 are display as () since that is the negative format. Things I’ve tried that haven't worked:
- "is not blank" and is not empty" filters.
- Ensured that “Show items with no data” was not enabled.
- Filtered out “zeros” using custom measures.
- Instead of using format expression, divided all measures by 1,000 – this goes against best practice so I don’t like it for that but it also caused our numbers to be wrong.
5 Replies
- DataNinja777
Super User
Hi delish_hins ,
The issue you're facing stems from the way Power BI handles custom formatting strings and data visibility in matrix visualizations. When you use a format expression like #,#,;(#,#,); to display values in thousands, Power BI does not treat values between -999 and 999 as blank. Instead, it formats them into visually empty cells, but the underlying data is still present. This is why rows with small values still appear in your matrix. To resolve this, you can create a custom measure that explicitly filters out rows where the rounded value is effectively zero after dividing by 1,000.
Here's an example of a measure you can use:
Display Value = VAR RoundedValue = ROUND(SUM(Table[YourValueColumn]) / 1000, 0) RETURN IF(ABS(RoundedValue) > 0, RoundedValue, BLANK())In this measure, the values are first divided by 1,000 and then rounded to the nearest integer using ROUND. The IF statement ensures that only values outside the range of -999 to 999 are displayed, while smaller values are returned as BLANK(). This ensures rows with effectively zero values are not visible in the matrix.
Once this measure is added to your matrix visualization, it will only display rows where the rounded value is non-zero, addressing your issue. Additionally, make sure the "Show items with no data" option is disabled in the matrix visual settings, as this can force rows to appear even if their values are blank. This approach resolves the formatting issue while maintaining best practices for calculations and visualization.
Best regards,
Thank you. I've tried this approach but I ended up with significant rounding issues.
johnt75I think you could create a calculation group with a calculation item like
Filter low values = VAR InitialResult = SELECTEDMEASURE () VAR Result = IF ( InitialResult <= -1000 || InitalResult >= 1000, InitialResult ) RETURN ResultApply the calculation item as a filter on the matrix, and it should return BLANK for those values between -1000 and 1000, and so they will automatically be stripped out of the visual.
Thank you. I just came up with essentially the same solution. So far my numbers are tying out but I'm worried I'm going to end up with rounding issues because this logic is essentially throwing away values between -999 and 999. Any thoughts on that?
- johnt75
As your format string is already effectively removing those values then I don't think it would be a problem at the individual row level. It could cause issues at the total level, depending on how the totals are calculated, as the values between -1000 and 1000 would potentially be included in the total but not show up in the rows.
