Power BI report builder row group remove duplicates

[arifshah]

I have created a matrix in paginated report in report builder. There are some nested row groups that shows data in sub categories. In some situation, the data in the sub categories is same as the data in the parent categoreis even if you expand the group until lowest level. See the picture

Is there any way to remove the duplication and expansion of the hierarchy if no actual sub category exists?

[arifshah]

Hi,

Many thanks for the suggestion. The issue is that its a collapsable matrix so that hidden property can be toggled by the plus sign and then it will be shown again. Is there anway that we can disable further expansion of the tree if the child has the same value as the parent.

[v-nuoc-msft]

Hi @arifshah 

In the Reporting Services report builder, which is typically used to create paginated reports, you can solve this problem by modifying the group's visibility property. Here are some steps to consider:

Toggle visibility based on data:
Go to the group properties in the row group pane.
Find the "Visibility" section.
Sets a "hidden" expression that evaluates whether the current group's value is the same as the parent. If so, return True to hide the row.

Custom code:
You can write custom code to check if the child group's data is different from the parent group.
This code is then used in the group's Visibility expression to determine whether the group should be displayed.

filter:
Apply a filter at the group level to exclude rows where the subcategory data is the same as the parent category data.

Parent-child functions:
If you are using a dataset that supports parent-child hierarchies, make sure that the parent-child relationship is defined correctly so that the report builder does not create extra rows.

Check the dataset:
Sometimes, the problem can be due to the structure of the data set or the way the query is written. Make sure that the data set contains only necessary rows and that the query does not create duplicate data.

Row group expression:
Check the expression used for the row group. They should be set up in such a way that a new group is only created when the data changes meaningfully.

Here is an example of a visibility expression you might use:

code=IIF(Fields!SubCategory.Value = Fields!Category.Value, True, False)

This example assumes you have fields named SubCategory and Category. This expression compares two fields and if they are equal, the row will be hidden.

Regards,

Nono Chen

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