Forum Discussion
Power BI Report Builder - Grouping Issue
Hi Anonymous ,
Regarding the expand/collapse functionality, you’re right that it’s available by default only when using the Matrix Wizard, but you can still enable this feature manually within your current design. To achieve this, open the Design View and select the row group (for example, your first dimension level) in the Row Groups pane. Then, go to Group Properties - Visibility. Set the Initial visibility to Hidden if you want the group to start collapsed, and under Display can be toggled by this report item, select the textbox from the parent group header that will control the expand/collapse behavior. Repeat this step for each sub-group, ensuring that every child group’s visibility is toggled by its parent’s textbox. This setup will allow hierarchical expand/collapse just like the matrix wizard provides. Also, make sure that your row groups and detail rows use the same dataset fields to maintain correct aggregations and prevent mismatched values like before. If your main requirement is simply to keep the matrix structure while ensuring interactivity, another option is to start with the Matrix Wizard, generate the layout, and then switch to Design View to customize expressions or remove unwanted totals this way, the toggling functionality remains intact. This approach gives you both correct grouping behavior and dynamic expand/collapse control without depending entirely on the wizard defaults.
Hi Anonymous ,
Thanks for reaching out to Community Forum.
From your first screenshot, all five row groups are at the same level in the Row Groups pane. This means they are independent groups, not nested. When groups are at the same level, report builder repeats data because it doesn’t know which group should be inside another.
To fix it, you need to create a hierarchy so that each group is a child of the one above it, like this:
PL_Statement_L1
└─ PL_Statement_L2
└─ Account_Global_Internal_A_Name
└─ Global_Account_Name
└─ Local_Account_Name
To do this, rightclick a parent group in the Row Groups pane, choose Add Group - Child Group, and pick the next field in your hierarchy. Make sure each group’s Group on is set to the correct field from your data. Once that’s done, the report will show the correct values under each parent group, and the duplicates will go away.
Thank you.
Hi v-sshirivolu
Thank you very much fot your answer!Related to your information, I think my 'Row Gropius' are already nested. They are in the hierarchy you mentioned
Each nested to another above. Of course I have tried to do as you sugested by doing right click on a parent group and create a child group. Fo example:
Right click on a PL_Statement_L1 and selected PL_Statement_L2 Child Group.But still unfortunately I see repeated values next to the each row.
hI Anonymous ,
Try these steps: In Row Groups, remove any standalone (Details) group (right-click and choose Delete Group… then Delete group only). Make sure your five groups are properly nested, with each one indented under the previous: PL_Statement_L1 - PL_Statement_L2 - Account_Global_Internal_A_Name - Global_Account_Name - Local_Account_Name.
Check Group Properties for each and confirm the Group on field is correct, adding multiple fields if you need a composite key. In header cells, use values with scope to avoid repeats, like =First(Fields!PL_Statement_L2.Value, "PL_Statement_L2"). Also, match Sorting to the fields used in Group on for each level.
Lastly, check that your dataset isn’t duplicating rows by running a SELECT DISTINCT on the five hierarchy columns or testing with a simplified query. Following these steps should fix the repeated values issue in nested matrices.
Hi v-sshirivolu
I have deleted as you suggested all Row Groups.
What do you mean by "Make sure your five groups are properly nested, with each one indented under the previous" that after deleting I should add once more Row Groups?