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 @Anonymous '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

[mmichaels1970]

Holy cow...when you said:

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.

That was big to me!  I've been wondering why my pivot table wasn't enforcing relationships.  I was just dipping my toe in the water by dragging "company name" into the Filters Section and "Company Items" into the Rows section and I was wondering why I was seeing every item for every company instead of just those for the select company....ie a cross join.  I drag any field into the "values" section of the pivot table and WHAM....relationship enforced.

took me an hour to find this post.  But it helped, and even though it has a bit of age on it, I felt it important to say thank you.

[OwenAuger]

@mmichaels1970  Great to hear this helped you!

Love it when "older" posts come in useful to someone 🙂

@TakeoffGoatMigo - I just noticed your comment! With a simple star schema model, I believe a single measure that aggregates the fact table would be sufficient to handle this issue on any pivot table. However, in more complex models, you may need different measures for different purposes.

I believe the logic that Power BI applies when auto-generating a measure is to follow relationship paths from each table with columns used in the visual, find the "closest" table common to all paths, then apply COUNTROWS to that table, but would need to test that a bit more.

[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?

[Anonymous]

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