Power BI Report Builder - Grouping Issue

[ddamian91]

Hi,

I have an issue with my first report. I need 5 dimensions in the 'Row Groups' pane.

All of the fields are nested, so should be depended on the 'parent' group.

All of the fields are from the same dataset.

What the issue is?

Once I expand groups I got the same values per each line. Looks like relation is not created or something, but this should not be a reason beacuse all of the fields are from the same dataset.

Do you have any idea why this may happen?
This is how the report is set up in the Design mode:

[v-sshirivolu]

Hi @ddamian91 ,
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.

[ddamian91]

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.

[v-sshirivolu]

hI @ddamian91 ,

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.

[ddamian91]

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?

[v-sshirivolu]

Hi @ddamian91,

The repeated values are likely appearing because your Row Groups aren’t nested properly or your dataset has duplicate records. Since you’ve deleted the existing groups, recreate them in the correct order to reflect your hierarchy, starting with PL_Statement_L1, then PL_Statement_L2, followed by Account_Global_Internal_A_Name, Global_Account_Name, and Local_Account_Name. In the Row Groups pane, add PL_Statement_L1 as a parent group, then add each of the others as child groups under the previous one so the structure is properly indented.

After creating them, right click each group and check Group Properties to confirm the “Group on” field is set correctly. If a (Details) group still exists, delete it  just the group only, not the rows ...since your lowest level already shows the detailed data. In the matrix design, align the fields correctly with the group headers, and if you see values repeating, use the First() function (like =First(Fields!PL_Statement_L2.Value, "PL_Statement_L2")) to show each value only once per group.

Finally, make sure sorting for each group matches its field, and if the issue persists, check your dataset for duplicates by running a SELECT DISTINCT query on the five hierarchy columns. Once done, preview the report and expand each level to confirm the hierarchy displays correctly without repetition.

Thanks,

[ddamian91]

hi @v-sshirivolu 
I have tried your suggestion and now it works! But this solution made another problem, bacause my stakeholders needs expand/collapse buttons on each level of dimenssion. As far as I found this is only available in the 'matrix wizard' - and that's why I used this at the beginning (but this made an issue with values 😅 as we discussed in the previous messages).

[v-sshirivolu · Accepted solution]

Hi @ddamian91 ,

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.

[ddamian91]

hey @v-sshirivolu 
Have done as you suggested but seems, second level of nested group has once more the issue with aggregating lines:

and then when I expand to another level I see the same "Internal PL Name", which aggreagtes wrong nesting.

[v-sshirivolu]

Hi @ddamian91 ,

Thanks for trying the earlier steps. If the second-level group is still aggregating incorrectly, it usually means one of two things, either that group is still using the wrong field to group on, or the dataset has duplicate level-1/level-2 combinations. Even if the groups look nested correctly in the designer, Report Builder can still end up grouping on the same field (like “Internal PL Name”) at more than one level, which would explain why you’re seeing repeated values. Please open the Group Properties of the second-level group and double-check that both the Group on and Sort settings point only to the correct level-2 field and not the parent field. If those are set correctly, the next thing to verify is the data itself, running a quick SELECT DISTINCT on your level-1 and level-2 fields will tell you whether any duplicate pairs exist, since even one duplicate can cause the behavior shown in your screenshot. An easy way to narrow it down is to temporarily keep only the first two groups and preview the report. If level 2 still repeats, the issue is with its grouping or the data, if it doesn’t, the problem is coming from a lower level. 

[v-sshirivolu]

Hi @ddamian91 ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[v-sshirivolu]

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[pankajnamekar25]

Hello @ddamian91 

ncorrect group expressions – Each group must use its own field (e.g., =Fields!Region.Value, =Fields!Country.Value, etc.).Wrong hierarchy setup – Add each as a child group, not a parent.Detail row duplication – Remove or correctly group the detail row.Duplicate rows in dataset – Check your dataset; duplicates cause repeated values.

Ensure groups are properly nested and each uses the correct field for grouping.

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[ddamian91]

Hi @pankajnamekar25 
Thank you for your reply!
I created matrix via Matrix Wizard, I haven't create hierarchy by myself.
So each nested field might be a child group of previous one.
Expression of each field also looks correct:

Ensure groups are properly nested and each uses the correct field for grouping.

What do you meant by this?