Can't create relationships with good filtering

[student99]

I want to compare foster 2017 with foster 2016.
I will filter on type, country, customer, regio and ProductSerie.
(so i tried to link type form 2017 to type 2016, country with country, region to region,.. but its all many to many)
But these columns are many to many. I tried a lot with new tables,..
if I filter on Type then the country filtering is correct.
But the if i select spain then i see al the regions from the whole table (from UK, GER, FRA) -> my filter through doesnt work (filter Type -> country -> region -> Customer -> ProductSerie)
Can someone help me with this?

[TomMartens]

Hey,

to me it seems that both tables have almost the same structure for this reason, I would choose a little different approach.

I would create a single table from both of your source tables, doing this it's necessary that both have exactly the same structure.

I would start with creating a new column in both tables and call this table SourceFile. For the tablre "Forster AG 2016" I would apply the constant value "Forster AG 2016" and for the other table the value "Forster AG 2017".

Then I would ensure that both tables have the structure, this includes identical column names, saying this I would rename the column "Bruttoumsatz" from the "Forster AG 2016" table to "Turnover".

If both tables have the same structure you can use "Append Queries" function from the Combine Ribbon of the Home menu in Power Query (it's on the far right side) 🙂

Now you can use the column SourceFile to combine both tables and of course all the other columns to slice to your new table.

Hopefully this gives you an idea

Regards

Tom

[Anonymous]

If for whatever reason you cannot do the appending solution provided earlier by TomMartens you could add a few dimension table(s) then create relationships from both fact tables to these dimension tables.

The appending sollution is better and more scalable if say you wanted to add 2018 data in the future to this model. This is just an alternative suggestion if that method is not possible.