Multiple Relationships Between Tables

[Dimis_e]

Hello,

I am pretty new in power bi with some knowledge from here and there and a lot of google search... My issue is this:

I have two tables (one with actuals and one with targets) both with columns "Region" and "Country". Both tables have multiple inputs for "Country" and "Region".

I want to make graphs (actuals and targets) with data from the two tables and use slicers "country" and "region" to suffle within the data on the graph. To avoid using two slicers for "country" i need to connect the "region" and "country" column from the two tables so that the graph understands this is the same field but i can only make one connection, not two.

I searched online but none of the posts i found is similar to my case and it does not help me. If you could give me some advice i would highly appreciate it.

[themistoklis]

@Dimis_e

Concatenate the 2 fields and make a join based on the concatenated field.

It will be 2 tables with many to many relationship

[LivioLanzo · Accepted solution]

Hi @Dimis_e

you need to create a model like the below. The top dimension table will filter both sales and actuals

[Dimis_e]

It works... I cannot believe i lost 1 day for such an easy solution! Can you also help me with the below:

I want to connect the same two tables with another column which is again common. I was thinking to put it at the newly created table as per your instruction but if i do that then the coutnry will create multiple records and i guess the connection will not work again. Any suggestion?

[LivioLanzo]

Hi @Dimis_e

it depends if this column logically belongs to the dimension we have created previously or not.

what does it look like?

[Dimis_e]

For the third connection I need to have for every country 4 parameters: Asset, People, Process and Technology, so this would look like Africa - Algeria - Asset Africa - Algeria - People Africa - Algeria - Process Africa - Algeria - Technology

[LivioLanzo]

Hi @Dimis_e

is it possible to post a sample data?

[Dimis_e]

Here is an example

[LivioLanzo]

Hi @Dimis_e

I would create separate dimension for Asset, People, Technology, Process 

[Dimis_e]

You mean seperate RegionCountry tables by each one of the 4 parameters?

[LivioLanzo]

Hi @Dimis_e,

is the table you've shown a dimension or a fact table. Could you show both the dimension and fact?

[Dimis_e]

The table is coming from a sharepoint list, so i guess it is a fact table