Excel Power Pivot Data Model

Question

I have a problem in creating correct model for my excel power pivot. Here is how my current data model relationships look like:

And I'm trying to make a pivot table containg Rows, Columns and Values from tables „PH“ and „IMS“, with Filters from the table „BU_Brick“.

Untill I add Filters, everything works fine, but when I add Filters from „BU_Brick“ table the following worning shows up:

And now I'm very confused. Which else relationships do I need? Of course, data in pivot doesn't change on filtering.

Can you add in a screenshot of the actual relationships you've created? For example, [Brand].[Brand] = [BU_Brick].[Brand]. Also, which table is the "Brand" field, that you're filtering on, coming from? — Dale Kube
[Brand].[Brand] = [BU_Brick].[Brand]; [Brand].[Brand] = [PH].[Brand]; [PH].[Product] = [IMS].[Product]; [IMS].[Brick] = [Brick].[Brick]; [Brick].[Brick] = [BU_Brick].[Brick]; Everything in filter is from BU_Brick table. — TamaraB
Try using the immediate Brick and Brand fields in the Brick and Brand tables as your filters. I've experienced the same problem in PowerPivot when trying to filter on fields that are more than one table away (can't explain why). — Dale Kube
Thank you, I tried that, and that works fine, but there's still a problem with the other two fields - Manager and Associate — TamaraB
I would suggest creating two more intermediate dimension tables, like Brick and Brand, to house the unique Manager and Associate values. You can then filter on these two. — Dale Kube

RADO RADO · Accepted Answer · 2017-05-08T04:34:30

Your model has no connection between BU_Brick and IMS tables. When you filter BU_Brick, PowerBI has no way of passing these filters to the IMS table.

One way of solving this issue is to change the connection between BU_Brick and Brick to bi-directional. Double-click the connection, and when "connection edit" window opens, change flter direction to "both":

The little arrow on the connector will change to dowble-arrow, and BU_Brick filters will now work.

Formally speaking, you need to enable a flow of filter context in many-to-many relations. This is a common situation, and here you can learn more:

Many-to-many relationships in Power BI and Excel 2016

Excel Power Pivot Data Model

1 Answers