CODEWITHSUNDEEP
powerbi
Nikol
Nikol

Reputation: 1

How to build relationships in PowerBI , so that slicer filters will work correctly?

I have question about relationships in power bi. example of model

There are tables: Calendar + Fact Revenue(Fact table) + Plan Revenue + Customers(Fact table) + Country

The problem is that Plan Revenue doesn't have Customers ID , so if I make slicer filter by Country from Customer table it doesn't show correct Plan Revenue for each country. But if I make slicer filter by Country table, then it doesn't filter by dates to show fact revenue. I can not make relationship from Plan Revenue with Customers table, as each has many to many for Country.

Dates are in correct type, all relationships many to one, all are active. Is there any idea?

Thank you

Upvotes: 0

Views: 285

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

Ideally, you would have Country or Country ID in the Fact Revenue table, and then add a relationship to your Country table. This follows Star Schema.

Otherwise, you could add a relationship between your Customers table and your Country table, which would then make it a Snowflake schema.

Ask yourself if revenue follows a customer or where the transaction took place (think of customers with transactions in multiple countries). A Fact table is fact (doesn't change), so think about what if a Customer's country changes. (Nudging you to go for the first option.)

You will also need to update the relationships between:

  • Calendar and Plan Revenue
  • Country and Plan Revenue

Both of these are 1-1 and as a result these are bi-directional, and therefore when you slice on Country, it will advertently also filter Calendar via Plan Revenue.

Update both of these to be:

  • Calendar 1:* Plan Revenue Single cross-filter

  • Country 1:* Plan Revenue Single cross-filter and also ensure the same for:

  • Country 1:* Customer Single cross-filter

Upvotes: 0

Related Questions