Relationship Help - URGENT

[Syndicate_Admin]

Good afternoon it's been quite a while since I played Power Bi I did course and I saw videos and the truth that this is getting me out of my way...

I have the following structure to make the report but I do not understand why half of the tables are not linked so much for some things as for others...

I leave you a picture of the relationship and explain the steps to be done to be told why all this is not working for me...

I proceed to explain to see if they tell me how many relationships I lack or why some are not made to me...

The whole environment of these tables is intended to know since it is loaded into a vehicle x number of route sheets how many things I take the vehicle to how many customers with how much money he returned and which drivers carried things...

Relationships are given as follows

1 Vehicle - Many LeavesRutaSalida (linked by VehiculoID)

1 WorksheetSalida - 1 FactSheet(Linked by HojaRutaID the problem here is that we have many outputs that are made in the day or the next that have date data and I do not know if that is causing problem)

1 BladeRuta - 1 BladeRutaDetalle (Linked by HojaRutaID but within it are the details that tell the amount of outputs that could be made in each of the roadmaps and how the status of each shipment was)

HojaRutaDetalle - Many Shipments (The Link is given by EnvioID as it is the only way to bring the shipment data)

1 Shipping - 1 CustomerCtaCte (The link is given by EnvioID also for all receipts that are Invoices A or Invoices B)

1 Customer - Many Shipments (Linked by CustomersCtaCte this is done in this way since that field indicates who is the one who will pay the shipments therefore I take this value to indicate other types of conditions)

1 Employee - Many Roadmaps (Linked by EmployeeID but in the reality model each Roadmap is assigned a unique Driver)

1 Branch - Many Roadmaps (Linked by Branch OFFICE)

I hope it is half clear with the issue of relationships but for example when I want to link the shipment with customersctacte does not leave me the same thing happens to me with shipments and hojarutadetalle.

Generate the Dates table so that I can link dates but also when linking some dates from other tables the relationships do not take them to me...

Where am I having the problem or can you tell me how to find the whole solution? I already have armed all the calculations measured and etcs but without the filters that behave it is impossible to deliver this.

Thanks a lot.

[v-yangliu-msft]

Hi  @Syndicate_Admin  ,

Has your problem been solved? If it is solved, you can mark the correct answer as a mark

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

@MarianoD

First of all, I recommend that you differentiate between what are dimension tables vs data tables or facts. Dimension tables are for filtering data tables (in measures, filters, and so on). Data or fact tables are those that contain values on which calculations are to be performed (usually contain numeric fields).

Another recommendation is that you visually sort the dimension tables at the top half of the model sheet and the data tables at the bottom. For example:

By structuring it like this, it's easier to identify which type of table each is, and lets you see how relationships filter related tables.

In general (except for the calendar table) dimension tables contain very few fields (not to say just one) and "never" contain numeric data fields on which calculations are performed (there are always exceptions but as a rule it is)

If you have multiple data tables with common fields, you must create dimension tables with these common fields (unique values), and join them tables with all data tables that contain fields (those in the dimension table are unique values) in common.

If you can sort your model by this example it will be easier to understand that it can be improved.

[Syndicate_Admin]

So I have to split ALL the boards assemble each filter to generate this type of structure?

From what I show you there are no size tables only the calendar table I think has that kind of data. All the tables listed are tables brought from my database that I have in my company but I do not understand why I can not relate everything are simple links...

I would need you to help me a little more if I have to do this step since I never generate this type of table...

[Syndicate_Admin]

@MarianoD

Show me... what I'm proposing is a suggestion that will help you build relationships between fields and prevent conflict relationships from being generated.
the process is simple to do in power query. But the first thing I recommend is to identify the common fields: for example Customer ID, Branch ID etc... these are the fields with which dimensions are generated.
The problem with how tables look now is that following relationships is confusing.
To begin with, can you please the tables that have the connection of 1 above and those that have several connection below? (And post the image)

and then explain which fields you want to join and don't let the program...

by the way, creating a robust model BEFORE you start making measurements, calculations etc is paramount. Otherwise, you encounter the problems you now suffer (and it will probably be time to redo measures etc...)

[Syndicate_Admin]

@MarianoD

By the way, to better understand it:
Handling multiple data/fact tables

Introduction to data modeling

The Do's and Dont's of Power Bi relationships