HOW CAN I CONNECT THESE TABLES?

[Anonymous]

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!!

[TomMartens]

Hey @Anonymous ,

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

[Anonymous]

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 

[TomMartens · Accepted solution]

Hey @Anonymous ,

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

https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EWhkTNN5w9FPuxMfvm5CRksBPw6v-IJFmnvr_wwzitLhgA?e=1WYaN8

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

In the Model view

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:

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

:Edit the relationship and change the Cross filter direction:

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 

[Anonymous]

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