Problem aggregating across many to many link table in Power BI desktop

[YorkshireMan]

I would attach the pbix file but this forum won't allow attachments so screenshots will have to do.

In the data model I have to create reports against there are some many to many relationships. In the database these are catered for by using link tables. Power BI Desktop has an issue with these relationships. To illustrate the problem I took some screenshots of Power BI Desktop against some simplified dummy data.

The first shows the data model. There are many-to-many relationships between SubCategory and Item and between Item and Container that the two link tables reproduce:

Whenever you try and put together a visual using information from the Category, SubCategory, Item, and Container, tables Power BI can't cope. It says it can't determine a relationship between two, or more, fields:

However, if you add a DAX measure and put that in the visual, data is displayed:

The formula for the DAX measure is

Item Count = IF( COUNT( 'Item'[ItemId] )=BLANK(), 0, COUNT( 'Item'[ItemId] ) )

But the data displayed in the visual isn't correct. The visual appears to have made some bizarre cartesean product of the all the data combinations. Running a SQL query on the same data produced what I would expect to see. To test that I'd done the query correctly I did the same exercise using Qlik Sense, which did handle the data as I would expect and did match my query results:

[v-qiuyu-msft]

Hi @YorkshireMan,

I have reported this issue internally: CRI 43422471. Will update here once get any feedback.

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @YorkshireMan,

Please see below information from PG:

The user is hitting a current limitation in Power BI.  You cannot build a single visual having columns from tables with an N:1:N, even if the relationships have bi-directional cross filtering, unless you also have a measure, or aggregate, in the visual.  As the user has discovered, the workaround is to include at least one measure, or aggregate, in the visual. 
 
Ideally, we would improve this behavior.  However, we have not yet found a behavior that we like that works across arbitrary model structures.  Therefore, I cannot provide an estimate for when we might fix this.  I suggest the user add a measure, or aggregate, to their visual to workaround this problem.  One option is to drag the same column into the visual twice and switch one of the columns to use a Count aggregate.

Best Regards,
Qiuyun Yu

[YorkshireMan]

Hi @v-qiuyu-msft,

Thanks for the updates. However, the suggested work around is incorrect. Dragging the same column twice in to the visual and changing one to use a Count aggregate still results in the visual complaining about not being able to detertmine a relationship between fields; data is only displayed in the table if a DAX measure is used. However, changing the visual to a bar chart does display but the data is incorredct. As I mentioned in the initial posting, the data that does display is incorrect as the table displays a cartedean product of all combinations of the data instead of the relationships that actually exists; so doing a Count aggregate displays incorrect data. 

I've tried a using other visual applications (namely Qlik View and Qlik Sense - see screenshot in original posting) and they display the data correctly.

Regards,

Richard.

[v-qiuyu-msft]

Hi @YorkshireMan,

I have created a pbix file with dummy data, you can download here. You can see it return count values fine.

Best Regards,
Qiuyun Yu

[YorkshireMan]

Hi @v-qiuyu-msft,

Thanks for the link to the pbix file; using dropbox is a good idea if I'd thought of it originally then I would have put a link in the original post.

Anyhow, your pbix file doesn't recreate the problem I'm experiencing. The dropbox link to my file is

https://www.dropbox.com/s/yszb534tlh72ucc/PowerBIProblem.pbix?dl=0

The first page (The Issue) is showing the basic table that I'm trying to create and the problem Power BI is having.

The second page (Adding a Count aggregate) shows that no data is displayed just using a Count aggregate.

The final page (Adding a DAX measure) shows that data is displayed when using a DAX measure and also shows that the data displayed is incorrect as it is a cartesean product of all combinations regardless of whether or not those combinations are in the data.

In case I'm using an out of date version of Power BI desktop the version I'm using is:

Version: 2.48.4792.721 64-bit (July 2017)

[v-qiuyu-msft]

Hi @YorkshireMan,

I have sent your pbix file to consult PG internally, will update here if I get feedback.

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @YorkshireMan,

Please check below response from PG internally: 

That new PBIX has very different cross-filter direction flags on the relationships. With this new model structure, there is no table in their model that is filtered by all of the columns they want to include in their visualization. Therefore, they cannot include all those columns in a single visualization without getting a Cartesian product.

What combinations does this customer actually want to see in their visual? Do they want to see all of the (Category | SubCategory) and (Container) combinations where there is at least one Item in common? If so, they need to switch the relationship between (SubCategoryItemLink and Item) and (ContainerItemLink and Item) to have Cross filter direction Both.

If they want some other result, they need to setup the relationships so that there is some table in the model that is cross-filtered by all the columns they want to visualize. A count on that table will constrain the combinations. They can also write a DAX measure that encodes their logic using advanced constructs like LookupValue. The important thing is that the measure produces a null/blank value for combinations that they do not want to show. However, I would suggest getting the relationship structure correct first, rather than attempting complex logic in a DAX measure.

Best Regards,
Qiuyun Yu

[Anonymous]

This seems to still be a limitation of Power BI?

If I connect to the same Data Model with Excel, however, I can create a pivot table with columns from tables with an N:1:N with no Measure and no Error.