Forum Discussion
Remove blank rows from matrix but still contain data
I stuck in the matrix visual to remove blank rows in the matrix.
I have the data that contain in 9 levels show as picture below,
I create the matrix as picture below.
I try to create the measure to remove blank row and keep the data but it still not working
How can I do, Please help.
Please see the file as link: State PBI
For now I cax fix the problem to hide blank row but when i click "+" into tje low level the data in column has dusappear again as picture
This is my measure
Hi Jidapa18
Assuming that's how you hierarchy is - once an upper level hierarchy is already blank the rest are expected to be blank - create a column that counts the number of nonblank levels or turn this into a measure.
NonBlank Levels = COUNTROWS ( FILTER ( { State[Level1], State[Level2], State[Level3], State[Level4], State[Level5], State[Level6], State[Level7], State[Level8], State[Level9] }, NOT ( ISBLANK ( [Value] ) ) ) )You also need to enable subtotals. You can't make the values appear in the upper levels if subtotal is not enabled.
Create a measure that will check whether the current hierarchy number matches the max number of hierarchy levels.
RemoveBlankRows 2 = VAR VisibleLevel = SWITCH ( TRUE (), ISINSCOPE ( 'State'[Level9] ), 9, ISINSCOPE ( 'State'[Level8] ), 8, ISINSCOPE ( 'State'[Level7] ), 7, ISINSCOPE ( 'State'[Level6] ), 6, ISINSCOPE ( 'State'[Level5] ), 5, ISINSCOPE ( 'State'[Level4] ), 4, ISINSCOPE ( 'State'[Level3] ), 3, ISINSCOPE ( 'State'[Level2] ), 2, ISINSCOPE ( 'State'[Level1] ), 1 ) VAR _nonBlankLevels = SELECTEDVALUE ( State[NonBlank Levels] ) RETURN IF ( VisibleLevel = _nonBlankLevels, MAX ( State[ID] ) )Note: If the goal is to display only the deepest non-blank level in a hierarchy while hiding all upper levels, that isn’t possible. To reach any lower level in a hierarchy, the upper levels must first be present and expanded.
5 Replies
- danextian
Super User
Hi Jidapa18
Assuming that's how you hierarchy is - once an upper level hierarchy is already blank the rest are expected to be blank - create a column that counts the number of nonblank levels or turn this into a measure.
NonBlank Levels = COUNTROWS ( FILTER ( { State[Level1], State[Level2], State[Level3], State[Level4], State[Level5], State[Level6], State[Level7], State[Level8], State[Level9] }, NOT ( ISBLANK ( [Value] ) ) ) )You also need to enable subtotals. You can't make the values appear in the upper levels if subtotal is not enabled.
Create a measure that will check whether the current hierarchy number matches the max number of hierarchy levels.
RemoveBlankRows 2 = VAR VisibleLevel = SWITCH ( TRUE (), ISINSCOPE ( 'State'[Level9] ), 9, ISINSCOPE ( 'State'[Level8] ), 8, ISINSCOPE ( 'State'[Level7] ), 7, ISINSCOPE ( 'State'[Level6] ), 6, ISINSCOPE ( 'State'[Level5] ), 5, ISINSCOPE ( 'State'[Level4] ), 4, ISINSCOPE ( 'State'[Level3] ), 3, ISINSCOPE ( 'State'[Level2] ), 2, ISINSCOPE ( 'State'[Level1] ), 1 ) VAR _nonBlankLevels = SELECTEDVALUE ( State[NonBlank Levels] ) RETURN IF ( VisibleLevel = _nonBlankLevels, MAX ( State[ID] ) )Note: If the goal is to display only the deepest non-blank level in a hierarchy while hiding all upper levels, that isn’t possible. To reach any lower level in a hierarchy, the upper levels must first be present and expanded.
- alish_b
Hey Jidapa18 ,
Well, one approach could be to see if the category in scope is blank or not, and if yes, get the measure to return blank in that scope (and blank is something Power BI will filter out). With that logic in mind, I created one measure for computing the first ID as follows (tweak around to match your data):
First ID = IF( (ISINSCOPE(State[Level9]) && (ISBLANK(SELECTEDVALUE(State[Level9])) || SELECTEDVALUE(State[Level9]) = "")) || (ISINSCOPE(State[Level8]) && (ISBLANK(SELECTEDVALUE(State[Level8])) || SELECTEDVALUE(State[Level8]) = "")) || (ISINSCOPE(State[Level7]) && (ISBLANK(SELECTEDVALUE(State[Level7])) || SELECTEDVALUE(State[Level7]) = "")) || (ISINSCOPE(State[Level6]) && (ISBLANK(SELECTEDVALUE(State[Level6])) || SELECTEDVALUE(State[Level6]) = "")) || (ISINSCOPE(State[Level5]) && (ISBLANK(SELECTEDVALUE(State[Level5])) || SELECTEDVALUE(State[Level5]) = "")) || (ISINSCOPE(State[Level4]) && (ISBLANK(SELECTEDVALUE(State[Level4])) || SELECTEDVALUE(State[Level4]) = "")) || (ISINSCOPE(State[Level3]) && (ISBLANK(SELECTEDVALUE(State[Level3])) || SELECTEDVALUE(State[Level3]) = "")) || (ISINSCOPE(State[Level2]) && (ISBLANK(SELECTEDVALUE(State[Level2])) || SELECTEDVALUE(State[Level2]) = "")), BLANK(), MIN(State[ID]) )You should be able to extend similar logic for other fields.
Hope it helps! - grazitti_sapna
Hi Jidapa18,
You can try below solutions:-
Turn off "Show Items with no Data"
-
Select the Matrix visual
-
In Rows, click the dropdown for each hierarchy level
-
Make sure “Show items with no data” is OFF
You must check this for all 9 levels, not just the top one.
Filter out BLANK values at the visual level
-
Select the Matrix
-
Go to Filters pane
-
Add each hierarchy column
-
Set filter:
-
is not blank
-
Update your measure like this
My Measure =
VAR v = SUM(Fact[Value])
RETURN
IF(ISBLANK(v), BLANK(), v)🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together! -
- krishnakanth240
Hi Jidapa18
Create a measure that has data
Has Data =
NOT ISBLANK ( [Base Measure] )
Use this measure into 'Filter on this Visual' and set it to True/1
Also, turn off - 'Show items with no data' for row fields from settings
