Hide blank rows of a hierarchy in a paginated report matrix

[zax]

I have a dataset published to a premium workspace that I am using to create a paginated report using a matrix and trying to display Chart of Accounts in the correct order. Everything appears to be displaying correctly but I have some blank rows I need to remove (Ref 1). They only appear just under the expanded node and expand for remaining levels. When I create a Power BI report using the matrix visual (Ref 2), I get around this problem by using ISINSCOPE()/EntityRowDepth/EntityBrowseDepth method used here: https://www.daxpatterns.com/parent-child-hierarchies/

I can't seem to replicate this in a Paginated report, it seems so simple, but isn't working.

I have tried the following expressions in the 'row visibility...' for each level row changing the '2' for the correct level. I have also tried every variation I could think of and tried different fields.

=iif(Fields!ID_RowDepth_.Value = 2 And IsNothing(Sum(Fields!Balance__paginated_.Value)), True, False)

=IsNothing(Fields!Level_1.Value)

=iif(Fields!Level_1.Value="",True,False)

Ref 1 - View from Running report in Report Builder. The line hi-light I created an expression to show that is the row I want to hide, but I can't seem to apply it in anyway to actually hide it.

(expression show/hide: =iif(Fields!ID_RowDepth_.Value=2 and IsNothing(Sum(Fields!Balance__paginated_.Value)),"Hide","Show")

Ref 2 - Matrix in Power BI, no blank rows for any of the lines even as I expand down because of method described earlier

Ref 3 - Running DAX query in DAX Studio shows where the blank is coming from

Ref 4 - Matrix design view

Thank you.

[v-rzhou-msft]

Hi @zax 

Do you want to remove the hierarchy of the child level if it's blank?

I think it could't be accomplished,to make an assumption,if the child hierarchy has a child hierarchy,how does the data of the child child hierarchy show once you remove its parent hierarchy...so logically it's forbidden to do it.But you can do it in the design of the model to reduce the hierarchy levels. And I am confused about that in Power BI Desktop, hierachy level in matrix seems to work well and won't show blank child level. Maybe there are something wrong in Report builder, I suggest you to check your relationshop or your table.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zax]

@v-rzhou-msft  Thank you for your response. 

I'm building the hierarchy in Power BI using the PATH functions. Right out of the box in Power BI matrix, I see the blank rows for each child node, so that is when I used the technique of ISINSCOPE. That seems to work great in matrix, but if I convert to a table in Power BI then the table is completely blank, so I believe this is the issue since paginated essentially is a table view, but I can't seem to figure out how to copy a Power BI matrix to Paginated matrix.

I was hoping I could hide those blank rows WITH only blank childs, but doesn't seem to function this way.

[jhowe1]

I have exactly this problem. Blank rows seem to be dynamically hidden in power bi using methods in this article https://www.daxpatterns.com/parent-child-hierarchies/, I have not found a good solution to do the same in power bi paginated (SSRS). Did you find a good solution?

[zax]

@jhowe1 I did not find a solution unfortunately.