Cannot make relationship active

[Petanek333]

Hi,

I know there may be several workarounds but as I am trying to understand as much about Power BI as possible, I cannot get past this issue. 

I have two tables containing data scraped from czech eshops - "Alza CZ" table (A in the image) and "Datart CZ" table (B in the image). Both of them are connected to table "Jednotící tabulka" in a one to one relationship. 

Then there is another relationship I need to create. I need to sort a legend in the graph, so I created a table "Pořadí RRP" with unique values from columns Nad/Pod RRP in tables Datart CZ and Alza CZ in one column and numbers 1 to 5 in the second column so I can sort the legend.

And now it gets confusing for me. When I connect this table to "Alza CZ" table in a one to many relationship (C in the image), everything is OK, relationship is active. When I do the same with "Datart CZ" (D in the image), I get this message.

Can you tell me why is it happening? I understand there can be some issue with these interconnections, but why the relationship between table "Pořadí RRP" and "Alza CZ" can be made active but the same type of connection between "Pořadí RRP" and "Datart CZ" not. The tables "Alza CZ" and "Datart CZ" are almost identical.

[Petanek333]

---

@HotChilli   That is a great point with the fact and dimension tables and I will definitely clean the data. But in fact, the model acts in that way even though there are some unnecessary columns. 

Jednotící tabulka (table) is a fact table, tables like Alza CZ or Datart CZ are dimension tables.

Unfortunately I cannot see "2 paths linking the same 2 tables" like you. Jednotící tabulka - Datart CZ  (path A) is a fact table to dimension table type of connection and Pořadí RRP table - Datart CZ (path D) is a dimension table to another dimension table type of connection.

Maybe I am just missing something, but I cannot think of a way how to connect these tables differently. In Jednotící tabulka table there are unique Product IDs (column Product ID) with a recommended selling price. In other tables such as Datart CZ or Alza CZ there are prices that those eshops are using (scraped from web) and also an information (that is a function) if the eshop price is higher or lower than the recommended price. Pořadí RRP table just acts as a dimension table with a single purpose, namely to order those strings (price is higher, price is lower, price is the same) generated by a function in a logical way and this order should be used in a legend of graphs.

So to sum it up: 

Jednotící tabulka - fact table, using mainly Product ID and Recommended price

Datart CZ - dimension table, connected via Product ID to fact table, important columns are eshop price (scraped from web) and a function determining whether the eshop price is higher or lower than the recommended one, also lot of redundant columns in that table, I will fix that

Pořadí RRP - dimension table used to order strings (otherwise they are ordered alphabeticaly in the legend) containing information about the price being higher or lower

Maybe you could argue that since Jednotící tabulka has the same amount of rows as the other tables (Alza CZ, Datart CZ and many others) and many identical columns, I can merge it and have this one table (Jednotící tabulka) with just different columns for different eshops. This solution however is not good because I want to be able to refresh the tables one by one because it takes almost an hour to scrape 400 products from one eshop.

@PaulDBrown I cannot change the direction. This pops up.

---

[PaulDBrown]

@Petanek333 

You must first change the cardinality to one-to-many ("one" being the table doing the filtering) and then you can change the direction to "Single"

[HotChilli]

It means that there cannot be 2 paths linking the same 2 tables.  You can see from the diagram that between "Pořadí RRP" and "Datart CZ" there would be 2 paths if the relationship was allowed.

---

So you need a rethink on the model.  I can see a few relationships are 1:1 with dual direction and I can see that the same fields e.g. Brand, Category are stored in many tables.  Generally, those are bad ideas.

Try and identify the Fact tables and the Dimension tables and use a star schema.

I hope it helps

[PaulDBrown]

You need to change the direction of the relationships to single (you have at least a couple set to "both"). Generally it's advisable to avoid relationships with both directions. You should also consider setting the one-to-one to one-to-many in the properties pane. (This will avoid trouble if by chance the fact tables include duplicate values down the road).