cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danlindoso11
Helper II
Helper II

HOW CAN I CONNECT THESE TABLES?

Hello Everyone, how are u all?

 

I have a bit of problem here inside my power BI. I have an Order Table, that is where is stored all of my company orders, and there is a data column and a city column.

 

I also created a Calendar table as an auxiliar, and connected with the data column of the order table.

 

My boss asked me to put inside the power BI, not only the amount of orders that has been creating each day, but also our daily amount of orders goals, that I just put manually as another table, with a date column, a city column, and the orders goals column.

I tried to connect the order goals table with the order table, but when I tried to connect 2 collumns at the same time, it just doesn't work.

 

It only seems to work whether I choose to connect by city ou by data, but never both.

Does anyone have an idea that can pull this off?

Thank you very much in advance!!

1 ACCEPTED SOLUTION

Hey @danlindoso11 ,

 

thanks for uploading the pbix file. Here you will find my version:

https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EWhkTNN5w9FPuxMfvm5CRksBPw6v-...

 

To make it work, it's necessary to delete the relationship between goals and orders:

image.png

In the Model view

image.png

I created a new layout page, that helps to focus on a certain aspect of the model. A change performed on a layout page will of course be reflected in the data model itself, next to that different layout views help to focus on a certain aspect.

 

Then you can create relationships like this:

image.png

 

Next to that, you might consider changing the filter direction of the relationships from both to single (red to green)Edit 

image.png

:Edit the relationship and change the Cross filter direction:

image.png

The reasoning behind this, relationships other than 1-to-many with the Cross filter direction set to Single are considered weak relationships. Weak relationships can result in slow performance and sometimes also return unexpected results.

 

Hopefully, this helps.

 

Regards,

Tom 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

4 REPLIES 4
TomMartens
Super User
Super User

Hey @danlindoso11 ,

 

I recommend to create a city table, that contains the unique values from the order table and the unique values from the goals table, if you are using DAX you can use this statement:

city table =
DISTINCT(
    UNION(
        ALLNOBLANKROW( 'orderstable'[citycolumn] )
        , ALLNOBLANKROW( 'goalstable'[citycolumn] )
    )
)

Then you can use the Date Table to connect to the orders table (1-to-many) and to the goals table (1-to-many).

And use the city table to create also two one-to-many relationships.

From a star schema point of view you have a data model

  • dimension tables: date and city
  • fact tables: orders and goals

Here you will find an introduction to data modeling with Power BI: https://docs.microsoft.com/en-us/learn/paths/model-power-bi/

 

Hopefully, this provides the information to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hey Tom!!

I tried, but it didn't work. I created a city table, I connected the data table with those 2 tables, but when I try to connect the city table with the two tables as well, it says that is redundant, that I need to delete some connection in order to work.

I am attaching the file down below, if want check it out I will be very happy!

Thanks Tom!

https://drive.google.com/file/d/14I86HJ3ZTdZ6RPgSxSNqvFFnqVt4GTlc/view?usp=sharing 

Hey @danlindoso11 ,

 

thanks for uploading the pbix file. Here you will find my version:

https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EWhkTNN5w9FPuxMfvm5CRksBPw6v-...

 

To make it work, it's necessary to delete the relationship between goals and orders:

image.png

In the Model view

image.png

I created a new layout page, that helps to focus on a certain aspect of the model. A change performed on a layout page will of course be reflected in the data model itself, next to that different layout views help to focus on a certain aspect.

 

Then you can create relationships like this:

image.png

 

Next to that, you might consider changing the filter direction of the relationships from both to single (red to green)Edit 

image.png

:Edit the relationship and change the Cross filter direction:

image.png

The reasoning behind this, relationships other than 1-to-many with the Cross filter direction set to Single are considered weak relationships. Weak relationships can result in slow performance and sometimes also return unexpected results.

 

Hopefully, this helps.

 

Regards,

Tom 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Hey Tom! Thank you very much, you were extremly helpful!! Solution accepted!!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!