PBI Semantic model pivot table in Excel not recognising table relationships

[Chris_Black]

I have inserted a power bi pivot table into an excel file.

When I add columns from a single table in the data model everything works as expected.

However, if I try to add a column from another table in the data model instead of providing the related value from that column, it duplicates every row and provides every possible value against every line.

The two tables are linked with a 1 to many relationship in the PBI model and attempting to replicate this process within PBI desktop using a matrix works exactly as expected without this error.

This same error occurs using any combination of related tables regardless of the type of connection (one to one, one to many, single or bi directional etc.)

Is there anything that can be done to add multiple columns from related tables to a single pivot table without this occurring?

[Chris_Black]

Thank you both! This has solved the problem and a great explanation of why it happens too! 🙂

[OwenAuger · Accepted solution]

Hi @Chris_Black 

I was partway through replying when I saw @v-heq-msft 's reply so thought I would add a few comments.

I agree, this is on the face of it an unintuitive "feature" of the way Excel PivotTables behave when querying Power BI models, compared with Power BI Desktop.

Firstly, to answer your question, the solution within Excel would be either:

1.

(a) Create an appropriate measure in the Power BI model that returns a value (say 1) when the table on the many-side is nonempty. For example:

Non Empty Flag =
INT ( NOT ISEMPTY ( <ManySideTable> ) )

(b) Apply a value filter based on that measure to one of the fields in the PivotTable:

2. Actually display a suitable measure as a Value field in the PivotTable table.

Explanation:

When Row/Column fields but no Values are added to a PivotTable connected to a Power BI model, a crossjoin is created, unless the columns from the same table.

Here is an example MDX query generated by an Excel PivotTable where I have added Store[StoreKey] and Sales[StoreKey] to a PivotTable:

SELECT 
    NON EMPTY 
        Hierarchize
        (
            DrillDownMember
            (
                CrossJoin
                (
                    {
                        [Store].[StoreKey].[All],
                        [Store].[StoreKey].[StoreKey].MEMBERS
                    },
                    {[Sales].[StoreKey].[All]}
                ),
                [Store].[StoreKey].[StoreKey].MEMBERS,
                [Sales].[StoreKey]
            )
        )
    DIMENSION PROPERTIES 
        PARENT_UNIQUE_NAME,
        HIERARCHY_UNIQUE_NAME
     ON COLUMNS
FROM [Model]

Note the CrossJoin with neither filtering nor reference to any measures or aggregations.

However, if I create essentially the same "visual" in Power BI Desktop, the DAX query generated is:

DEFINE
	VAR __DS0Core = 
		SELECTCOLUMNS(
			KEEPFILTERS(
				FILTER(
					KEEPFILTERS(
						SUMMARIZECOLUMNS('Store'[StoreKey], 'Sales'[StoreKey], "CountRowsSales", COUNTROWS('Sales'))
					),
					OR(
						NOT(ISBLANK('Store'[StoreKey])),
						NOT(ISBLANK('Sales'[StoreKey]))
					)
				)
			),
			"'Store'[StoreKey]", 'Store'[StoreKey],
			"'Sales'[StoreKey]", 'Sales'[StoreKey]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(501, __DS0Core, 'Store'[StoreKey], 1, 'Sales'[StoreKey], 1)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'Store'[StoreKey], 'Sales'[StoreKey]

Note the aggregation added within SUMMARIZECOLUMNS:

"CountRowsSales", COUNTROWS('Sales')

Power BI has automatically decided to add a "measure" counting the rows of the table on the many-side of the relationship between the tables whose columns have been added to the visual. Within SUMMARIZECOLUMNS, this automatically filters the result to combinations that actually exist (by virtue of COUNTROWS ( Sales )  being nonblank).

Regards

[TakeoffGoatMigo]

Thank you for the solution and explanation!  Our organization is having this same issue.  If we want our whole dataset to be free from this bug, will we need to create a new measure for each set of relationships where this applies?

[v-heq-msft]

Hi @Chris_Black ,
Based on your description, first confirm that the relationships between tables are properly defined in the Power BI data model. Ensure that the relationships are active and correctly set up as one-to-many or many-to-one as needed. Next, make sure that the data model has been imported correctly into Excel. Sometimes, relationships may not be passed correctly, which can cause you problems when working with fields in related tables. Finally, try creating DAX metrics in Power BI Desktop to retrieve the values you need, rather than adding columns directly from the related table. Then, use those metrics in a pivot table. This usually solves the problem of not respecting relationships in Excel.

Best regards,
Albert He

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