2 Table Joins to a single table

[Anonymous]

Hi,  I have 2 tables that both need to be joined onto a DATE table , but not sure how I best do this. 

Table (Mort_Data)   
Variable (Mort_Year)   
Measure (Mort_Vol)

Table (Comp_Data)
Variable (Comp_Year) 
Measure (Comp_Vol)

Table (Date_Table)   
Variable (Date_Year)

I need to essentially have the design like below (open to suggestions though, of course).

The reason for needing this (I think, unless there is a better, easier way) is that one bar chart I need will require the X-axis to have the Year, but it will be the combimation of the COMP_YEAR and MORT_YEAR as I will have 2 values (Mort_Vol and Comp_Vol). This is the output that I am hoping to achieve in Power BI (taken from a mock in excel) and maybe it can be done another way?

I hope that all makes sense, any help on this would be hugely appreciated. 

[mwegener]

Hi @Anonymous ,

looks okay to me.

[Anonymous]

The problem is, I can't seem to join the 2 tables to the Date_Table, it won't allow me to have 2 active joins for some reason. I get this error message that I have no idea how to resolve. 

"an active set of indirect relationships already exists between these tables. To make this relationship active, set the Cross filter to "Single", delete, or deactivate any indirect relationship first"

[mwegener]

Hi @Anonymous ,

can you provide a screenshot of the model view?

Check that the relations are one to many (1:*) as in your drawing and that the filter direction points only to the fact tables Mort_Table and Comp_Table.

Also make sure that there is no other direct relationship between the Mort_Table and Comp_Table tables.

[Anonymous]

This is the model, as you can there is a solid line between MORT_TABLE and DATE_Table, but a dotted line between COMP_TABLE and DATE_TABLE and I can't seem to make this active. 

[mwegener]

Hi @Anonymous ,

the problem is the direct relationship between MORT_TABLE and COMP_TABLE.

Can you remove this?

[Anonymous]

I do need those tables joined together, sadly. Unless there is another way to do this, but doubt there will be. 

[mwegener]

Hi @Anonymous 

What is this many-to-many relationship for?

[Anonymous]

I needed the 2 volumes that come from those tables so assumed that I needed that join. If I was to remove it, would that data model allow me to pull back the variables needed as stated above?

[mwegener · Accepted solution]

Yes, the common axis / filtering is done via the Date_Table.

From both tables you get the corresponding aggregated value.

[Anonymous]

Yes, that make sense. I have just removed this and I do get the data I need, thank you.

However, by removing this, it now impacts another table because the join has disappeared. It's like one issue is resolved, but causes another issue elsewhere. 

I need my table to have data from the following tables
Dept_Table,
Sales,
Mort_Table
and Comp_Table

but now the join has been removed, I only have data from these tables
Dept_Table,
Sales,
and Mort_Table

as it won't allow me to bring in data from Comp_Table. Do I need to restructure the model somehow to capture?

[mwegener]

Yes, the best way to structure your data model is as a star schema
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema