Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.