Forum Discussion

han_rj's avatar
han_rj
Helper IV
1 year ago
Solved

Hide a row in Matrix

Hi Team,

 

I have created a matrix to show me Product wise sales for 2023,2024 and 2025.

I would like to hide rows where the sales of 2023 is 0.00 and the following years are "-"[No values]

 

Current View [ I want the highlighted row excluded]

 

Expected View

Measure Logic

Sales_2023 = COALESCE(SUM(Sheet1[2023 Sales])/1000000,"-")
Sales_2024 = COALESCE(SUM(Sheet1[2024 Sales])/1000000,"-")
Sales_2025 = COALESCE(SUM(Sheet1[2025 Sales])/1000000,"-")

 

 

Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    Hi han_rj ,
    To hide rows in your Power BI matrix where Sales_2023 is 0 and the following years have no values ("-"), create a new measure like this:

    Show Row =
IF (
    [Sales_2023] <> 0
    || [Sales_2024] <> "-"
    || [Sales_2025] <> "-",
    1,
    0
)

     


    Add this Show Row measure to your matrix as a visual-level filter and set it to show only when value is 1. This will hide rows where 2023 is 0 and the other years are blanks/"-".

     

     

    Power BI doesn’t support direct row hiding, but filtering using a measure like this achieves the desired effect.

4 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    Hi han_rj ,
    To hide rows in your Power BI matrix where Sales_2023 is 0 and the following years have no values ("-"), create a new measure like this:

    Show Row =
IF (
    [Sales_2023] <> 0
    || [Sales_2024] <> "-"
    || [Sales_2025] <> "-",
    1,
    0
)

     


    Add this Show Row measure to your matrix as a visual-level filter and set it to show only when value is 1. This will hide rows where 2023 is 0 and the other years are blanks/"-".

     

     

    Power BI doesn’t support direct row hiding, but filtering using a measure like this achieves the desired effect.

  • Sandip_Palit's avatar
    Sandip_Palit
    Super User
    1 year ago

    Step 1: Create the New Filter Measure
    First, create the DAX measure that will check for your condition.
    In Power BI Desktop, go to the Modeling tab or right-click your table in the Data pane.
    Select New Measure.

    Copy and paste the following DAX formula into the formula bar:

    Filter Rows =
    VAR Sales2023 = SUM(Sheet1[2023 Sales])
    VAR Sales2024 = SUM(Sheet1[2024 Sales])
    VAR Sales2025 = SUM(Sheet1[2025 Sales])
    RETURN
    IF(
    AND(Sales2023 = 0, ISBLANK(Sales2024), ISBLANK(Sales2025)),
    0,
    1
    )

    This code checks each row to see if Sales in 2023 are zero AND sales in both 2024 and 2025 are blank. It assigns 0 to these specific rows and 1 to all others.

    Step 2: Apply the Measure as a Filter
    Click on your Matrix visual to select it.
    Go to the Filters pane and find the Filters on this visual section.
    From the Data pane, drag your new measure, Filter Rows, into the "Filters on this visual" area.
    A new filter card will appear. Set the Show items when the value dropdown to is and type 1 in the box.
    Click Apply filter.

     

    If this explanation and solution resolve your issue, please like and accept the solution.

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi han_rj 

     

    I would have modeled this differently. Instead of  keeping separate columns for each year and then individual measures for those columns, I would unpivot so there's one column each for year and value and use format strings so zero are shown as  -. That aside, try this measure as a visual filter

    IF ( [Sales_2023] = 0 && [Sales_2024] = "-" && [Sales_2025] = "-", 1, 0 )