Forum Discussion
Helper IIIEliminating zero values from Matrix visual
Hi arunbyc
Yes, you can eliminate projects with zero Billed_Hours and Unbilled_Hours from your matrix visual without using the filter pane by modifying your DAX measures to return
BLANK()when both values are zero. When all measures in a row returnBLANK(), Power BI automatically hides that row in visuals like the matrix.Here's how you can adjust your measures:
Billed_Hours = VAR BilledHr = CALCULATE(SUM(project_time[hours]), project_time[is_billed] = TRUE) RETURN IF(BilledHr = 0 || ISBLANK(BilledHr), BLANK(), BilledHr)Unbilled_Hours = VAR UnbilledHr = CALCULATE(SUM(project_time[hours]), project_time[is_billed] = FALSE) RETURN IF(UnbilledHr = 0 || ISBLANK(UnbilledHr), BLANK(), UnbilledHr)Total_Hours = VAR TotalHr = [Billed_Hours] + [Unbilled_Hours] RETURN IF( ISBLANK([Billed_Hours]) && ISBLANK([Unbilled_Hours]), BLANK(), TotalHr )Explanation:
- Return BLANK for Zero Values:
- Each measure now checks if the calculated hours are zero or blank and returns
BLANK()accordingly.
- Each measure now checks if the calculated hours are zero or blank and returns
- Hide Rows with All BLANK Measures:
- In the matrix visual, any project where both Billed_Hours and Unbilled_Hours are
BLANK()will not display, as Power BI hides rows where all measures are blank.
- In the matrix visual, any project where both Billed_Hours and Unbilled_Hours are
- No Need for Filter Pane:
- This method embeds the logic within the measures themselves, eliminating the need to use the filter pane.
Additional Tips:
- Ensure 'Show items with no data' Is Off:
- In the matrix visual settings, make sure the option "Show items with no data" is turned off.
- Avoid Potential Confusion:
- By incorporating the logic into your measures, you reduce the risk of future confusion, as all filtering is handled within the DAX code rather than external filters.
Benefits:
- Simplified Maintenance:
- Keeping the logic within your DAX measures makes the report easier to understand and maintain.
- Improved Performance:
- Reducing reliance on visual filters can enhance report performance.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
- Return BLANK for Zero Values:
Helper IIIHi : Thanks for your reply. My isEmpty(project_time) measure inserted into the filter pane is doing the exact same thing you suggested. The zeros are actually blanks, so the filtered table will be empty. I am trying not to use the filter pane. I get worried that, at a later date, people may not realize there is something in the filter pane when troubleshooting any issues. (Happens to me many times). I am trying to see if there is any way to not use the filter pane but still get this result.
Super UserHi arunbyc
Yes, you can eliminate projects with zero Billed_Hours and Unbilled_Hours from your matrix visual without using the filter pane by modifying your DAX measures to return BLANK() when both values are zero. When all measures in a row return BLANK(), Power BI automatically hides that row in visuals like the matrix.
Here's how you can adjust your measures:
Billed_Hours =
VAR BilledHr = CALCULATE(SUM(project_time[hours]), project_time[is_billed] = TRUE)
RETURN
IF(BilledHr = 0 || ISBLANK(BilledHr), BLANK(), BilledHr)
Unbilled_Hours =
VAR UnbilledHr = CALCULATE(SUM(project_time[hours]), project_time[is_billed] = FALSE)
RETURN
IF(UnbilledHr = 0 || ISBLANK(UnbilledHr), BLANK(), UnbilledHr)
Total_Hours =
VAR TotalHr = [Billed_Hours] + [Unbilled_Hours]
RETURN
IF(
ISBLANK([Billed_Hours]) && ISBLANK([Unbilled_Hours]),
BLANK(),
TotalHr
)
Explanation:
- Return BLANK for Zero Values:
- Each measure now checks if the calculated hours are zero or blank and returns
BLANK()accordingly.
- Each measure now checks if the calculated hours are zero or blank and returns
- Hide Rows with All BLANK Measures:
- In the matrix visual, any project where both Billed_Hours and Unbilled_Hours are
BLANK()will not display, as Power BI hides rows where all measures are blank.
- In the matrix visual, any project where both Billed_Hours and Unbilled_Hours are
- No Need for Filter Pane:
- This method embeds the logic within the measures themselves, eliminating the need to use the filter pane.
Additional Tips:
- Ensure 'Show items with no data' Is Off:
- In the matrix visual settings, make sure the option "Show items with no data" is turned off.
- Avoid Potential Confusion:
- By incorporating the logic into your measures, you reduce the risk of future confusion, as all filtering is handled within the DAX code rather than external filters.
Benefits:
- Simplified Maintenance:
- Keeping the logic within your DAX measures makes the report easier to understand and maintain.
- Improved Performance:
- Reducing reliance on visual filters can enhance report performance.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!