Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Dimis_e
Regular Visitor

Multiple Relationships Between Tables

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.

1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

Hi @Dimis_e

 

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

 

2018-11-28_14-48-08.jpg

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

11 REPLIES 11
themistoklis
Community Champion
Community Champion

@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
Solution Sage
Solution Sage

Hi @Dimis_e

 

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

 

2018-11-28_14-48-08.jpg

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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?

Hi @Dimis_e

 

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

 

what does it look like?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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

Hi @Dimis_e

 

is it possible to post a sample data?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Here is an example

 

Capture.JPG

 

 

Hi @Dimis_e

 

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

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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

Hi @Dimis_e,

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.