Forum Discussion
Hide blank rows when data exists, otherwise show blank.
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!
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
3 Replies
- Kedar_Pande
Super User
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 LinkedInPerfect - just what I needed!
- 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
