Forum Discussion

JohnCavil's avatar
JohnCavil
Helper I
5 years ago

Data model help

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 🙂

Need Help

3 Replies

  • Gabriel_Walkman's avatar
    Gabriel_Walkman
    Continued Contributor
    5 years ago

    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's avatar
      JohnCavil
      Helper I
      5 years ago

      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.