Forum Discussion

kpct56591's avatar
kpct56591
Frequent Visitor
1 year ago
Solved

Hide Blank Rows not whole result

I am still learning Power BI, but this has to be one of the most frustrating parts of it. I am trying to hide Case Spend and Total Case Spend when it is blank for an item, but still show the Ship Qty and Gross Sales. In the picture example attached, item 00 has no case spend, while item 0 does. If I use the visual filter to hide Case Spend when blank or 0, it hides all of item 00 and only shows item 0. I have tried measures and still get the same result. I just want to collapse the two empty rows and save some space in the report.

 

The visual is a matrix, item number and the whited out line are rows, the rest are values shown as rows. Columns are months.

 

Need Help

8 Replies

    • kpct56591's avatar
      kpct56591
      Frequent Visitor
      1 year ago

      Hello, the visual level filter hides 00 completely, instead of just the empty rows.

  • ajaybabuinturi's avatar
    ajaybabuinturi
    Super User
    1 year ago

    Hi kpct56591,
    Coud you please try below measures 

    1. Create new "MetricsName" table using "Enter Data" options.
    2. Create measures like "Case Spend" and "Total Case Spend" if not created 
    3. Create a new measure like below 
    Matrix Value = 
SWITCH(
    SELECTEDVALUE(MetricsName[Metric Name]),
    "Ship Qty", SUM('Sales_Data'[Ship Qty]),
    "Gross Sales", SUM('Sales_Data'[Gross Sales]),
    "Case Spend", 
        VAR v = SUM('Sales_Data'[Case Spend])
        RETURN IF(ISBLANK(v), BLANK(), v),
    "Total Case Spend", 
        VAR t = [Total Case Spend]
        RETURN IF(ISBLANK(t), BLANK(), t)
)​

     

    3. Add the fileds as shown below and switch values to rows. Then uncheck the "Show items with no data".   

     

    Using above steps you will get required results.

     

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues. 

     

    • kpct56591's avatar
      kpct56591
      Frequent Visitor
      1 year ago

      Hello, this returned the following error:

       

      • ajaybabuinturi's avatar
        ajaybabuinturi
        Super User
        1 year ago

        Could you please provide me some sample data how your doing.