Excel connection to Power BI datasets, "Insert Pivot Table" button, does not keep the relationships

[TakeoffGoatMigo]

Within Excel, I am connecting to the Power BI datasets through the Get Data -> From Power Platform -> From Power BI

When doing so, there are two options to connect: "Insert PivotTable" and "Insert Table":

When "Insert PivotTable" is selected, the relationships in the dataset are not recognized, making this function useless.  The relationships are maintained when using the "Insert Table" option and provide correct data.

Does anyone else face this issue?  What can be done to get this to work as intended?

[TakeoffGoatMigo · Accepted solution]

Solved: Re: PBI Semantic model pivot table in Excel not re... - Microsoft Fabric Community

[lbendlin]

Did you mean to say this the other way round?  PivotTable = cube connection.  Table = DAX query.

[TakeoffGoatMigo]

Nope, I'm seeing the relationships are struggling to hold when using the "Insert PivotTable" connection with Power BI datasets.

The relationship between these tables seems to only be crashing when stacking fields in the "Rows" option and creating a hierarchy.  Here is what the pivot table shows when one field (Field A) is in the rows and the relating field (Field B) is in the values:

This is the correct value, as each should have a count of 1.

Here is are those same fields when both are in the rows field, creating a hierarchy:

The relationship no longer exists between these tables and all the values for Field B are showing, proving the lack of relationship.  When this same function is performed inside the Power BI application, this does not occur.  I have tested this with a variety of machines, datsets, tables, and fields and found that this error is consistent.

Have you seen anything like this before?

[lbendlin]

no, not familiar with that scenario or error pattern.  I hope someone else can help you further.