Hide blank rows when data exists, otherwise show blank.

[SeniorJunior]

Hi all, 

A strange one, I have the following table:

CustomerID	Product Name	Customer Status
1001	Laptop	Active
1002	Smartphone	Inactive
1003	Tablet	Active
1004		Active
1005	Headphones	Inactive
1001		Active
1006		Active
1007	Keyboard	Active
1008	Mouse	Inactive
1009	Laptop	Active
1001	Mouse	Active

And I want to use a Power BI table to display this information, but with a filter so that if the product name is available it will show, but if not, it will provide a blank row - BUT if there's a product, do not show a blank row.

So, the output would be

CustomerID	Product Name	Customer Status
1001	Laptop	Active
1001	Mouse	Active
1002	Smartphone	Inactive
1003	Tablet	Active
1004		Active
1005	Headphones	Inactive
1006		Active
1007	Keyboard	Active
1008	Mouse	Inactive
1009	Laptop	Active

So as you can see the blank row for 1001 has been removed, but the other customers who have blank but no other product details remain. 

How would you approach this? Would it be best to approach it by providing a comparison between MembershipID and Product Name and then if certain values are met it returns the product, or the blank row? Also note that the CustomerID could appear more than once for orders with different products and I would like this to be on different rows as opposed to amalgamated.

Thanks for any pointers! 

[Bibiano_Geraldo]

Hi @SeniorJunior ,
Add a calculated column in your data model to flag the rows you want to filter out. Use the following DAX formula:

IsRowToShow =
VAR CurrentCustomerID = 'Table'[CustomerID]
VAR HasNonBlankProduct =
    CALCULATE(
        COUNTROWS('Table'),
        'Table'[CustomerID] = CurrentCustomerID,
        NOT(ISBLANK('Table'[Product Name]))
    )
RETURN
IF(
    ISBLANK('Table'[Product Name]) && HasNonBlankProduct > 0,
    FALSE,
    TRUE
)

Once the column is created, apply a visual-level filter by dragging the IsRowToShow column to the filter pane and set the filter to show only rows where IsRowToShow is TRUE

[Kedar_Pande · Accepted solution]

@SeniorJunior 

Create a Calculated Column

ShowRow =
IF(
'Table'[Product Name] <> BLANK() ||
COUNTROWS(
FILTER(
'Table',
'Table'[CustomerID] = EARLIER('Table'[CustomerID]) &&
'Table'[Product Name] <> BLANK()
)
) = 0,
1,
0
)

Add the calculated column ShowRow to your table.
Apply a filter on the visual: ShowRow = 1.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[SeniorJunior]

Perfect - just what I needed!