Multiple Crossfilter Issue

[Anonymous]

Hello All, 

I'm developing a Month End Sales report for the international team where I'm comparing Year over Year Sales, Sales to Budgetting Revenue (Forecast) and Sales to Quota. I have 3 data tables: Sales, Budget, and Quota and I'm using the Distributor ID as the relationship column between the three tables. 

I've been asked to create a line chart visual to compare Sales to Budget and Quota so management can see the change in sales over time in comparison to budget and quota. All three tables have a dates column, but I know that there can be only one active relationship column between the tables. I've tried creating a fact calendar table, but I get an ambiguity error when linking the fact table to the 3 tables. Any suggestions on how to overcome the error and create the visual without impacting the other tables?

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Cross filtering both directions works well for a star schema as below.

However, cross filtering direction does not work well with a more general pattern with loops like below.

The above pattern can create an ambiguous set of relationships. For instance, if you sum up a field from TableX and then choose to filter by a field on TableY, then it’s not clear how the filter should travel, through the top table or the bottom table.

As with active/inactive relationships, Power BI Desktop won’t allow a relationship to be set to Both if it will create ambiguity in reports. There are several different ways you can handle this situation. Here are the two most common:

• Delete or mark relationships as inactive to reduce ambiguity. Then, you might be able to set a relationship cross filtering as Both.
• Bring in a table twice (with a different name the second time) to eliminate loops. Doing so makes the pattern of relationships like a star schema. With a star schema, all of the relationships can be set to Both.

For further information, please refer to the following document.

Create and manage relationships in Power BI Desktop 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@Anonymous,

I would structure your tables as a star schema.

Fact tables: Sales, Budget, Quota (no relationships between fact tables)

Dimension tables: Date, Distributor

Each dimension table should have a relationship with each fact table. Example of creating a matrix with this approach:

Use dimension table columns in matrix rows/columns.

Use measures (based on fact tables) in matrix values.