Data model help

[JohnCavil]

Hey guys,

I'm new to data modelling, and having some trouble setting up my data model. Here's my tables in a simplified model:

Table 1 - campaign / order / line_item

Table 2 - campaign / order / line_item

Table 3 - campaign / order / line_item / media

Table 4 - campaign / order / line_item / creative

Now i'd like to create a data model that holds "campaign" and "order" and "line_item" etc., in seperate tables that then link to all these other tables. So i'd want something like this:

Campaign Table - campaign

Order Table - order

Line_item Table - Line_item

So that if i pick a campaign in the "Campaign Table" then it filters all the campaigns in Table 1/2/3/4.  But i also want to filter it through to the Line_item Table. All i want is be to able to pick a campaign, have it filter all the tables, and show me all the corresponding line items for that campaign in every table. 

However, i can't get it to work the way i want it to. The cross filter direction won't let me set things up this way.  Is there any way i can set this model up so it works like this? Again, i'm sure i'm doing something wrong.

Hope my problem makes sense, thank you 🙂

[Anonymous]

Hi @JohnCavil ,

You could only have one active relationship between two tables. Make one of the relationship in active then use USERELATIONSHIP() function when you need to use this inactive relationship.

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive 

https://docs.microsoft.com/en-us/dax/userelationship-function-dax 

Best Regards,

Jay

[Gabriel_Walkman]

What did you try?

I'd guess you have to create a table with unique campaign values, then make a one sided relationship from that to the other tables. In power query, you can just right click a column name and choose "Remove Duplicates".

[JohnCavil]

I did this, however then i also had the "order" table. Also connected to all the tables.

I want both the campaign and the order table to filter eachother, so when a user selects a campaign, the list of orders updates. To do that i thought i needed a two directional relationship, so that campaign will filter my data tables, which will then filter the order table, and vice versa.

However, when i tried to do this Powerbi wouldn't let me, think it has something to do with data ambiguity and so on. Basically once i tried creating a 2 directional filter relationship with the data tables and the order table, after having done the same with the campaign and data tables, it wouldn't let me.