Forum Discussion
Eliminating 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:
Hi arunbyc
Yes, it is possible to eliminate projects with both billed and unbilled hours equal to zero from your matrix visual using a DAX measure. You can create a measure that checks if both Billed_Hours and Unbilled_Hours are zero and then use this measure as a filter in your matrix visual.
Here's how you can do it:
Create a Measure to Check for Zero Hours: Create a new measure that checks if both Billed_Hours and Unbilled_Hours are zero.
ShowProject =
IF ( [Billed_Hours] = 0 && [Unbilled_Hours] = 0, 0, 1 )
Use the Measure as a Visual Filter: Add this new measure to the filter pane of your matrix visual and set the filter to show only projects where ShowProject is equal to 1.
Here's a step-by-step guide:
Go to the Modeling tab in Power BI Desktop.
Click on New Measure.
Enter the DAX formula for the ShowProject measure.
Add the ShowProject measure to the filter pane of your matrix visual.
Set the filter condition to ShowProject is equal to 1.
This will ensure that only projects with non-zero billed or unbilled hours are displayed in your matrix visual.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Hi : 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.
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: