PBI Semantic model pivot table in Excel not recognising table relationships

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