Forum Discussion

arunbyc's avatar
arunbyc
Helper III
1 year ago
Solved

Eliminating zero values from Matrix visual

I have a matrix that shows three measures Billed_Hours, Unbilled_Hours, and Total_Hours for each project project. 

The formula for the measure Billed_Hours is :

Billed_Hours = var BilledHr= calculate(sum(project_time[hours]) , project_time[is_billed]=TRUE ) 
                                 Return  switch(true, chgHr = 0,0, BilledHr)
Unbilled_Hours measure is the opposite of the Billed_Hours. 
There are some projects with both billed and unbilled Hours equal to zero for whatever reason and I want to eliminate those projects from the matrix using a Dax measure that verifies if billed and unbilled hours are zero (it can check the Total Hours calculated column of the table as well) and eliminate these projects from the visual. Is it possible?
(Note: For now, I got this done by putting in a measure in the filter pane that checks for ISEMPTY(project_time) and returns 1 or 0  but I would like to know how to do this using Dax. Would appreciate your help)
@need help
  • VahidDM's avatar
    VahidDM
    1 year ago

    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 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.
    • 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.
    • 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!! 

     

    LinkedIn|Twitter|Blog |YouTube 

3 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    1 year ago

    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!! 

     

    LinkedIn|Twitter|Blog |YouTube 

    • arunbyc's avatar
      arunbyc
      Helper III
      1 year ago

      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.

      • VahidDM's avatar
        VahidDM
        Super User
        1 year ago

        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 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.
        • 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.
        • 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!! 

         

        LinkedIn|Twitter|Blog |YouTube