Combining Data From Unrelated Tables To Create Charts

[sangosteve]

I have two Unrelated Tables, the Invoices table and Deliveries table. The two have common fields such as the Date, Customer Code, Location and Line Tonnage. Ultimately I would want to create Charts based on the Line Tonnage from the two tables. For example I would want to have an Area Chart showing the total tonnage  per  month for the year 2024. What would be the best way way to go about this?Invoices TableDeliveries TableDesired Example Chart

[Jaaap]

HI Steve - here are five different ways to do this - there are pros and cons to each https://maxpowerbi.pro/facts-facts-facts-five-ways-of-combining-several-fact-tables-in-a-powerbi-rep...

[Stumpy_46 · Accepted solution]

My take is that these tables could & should be related. You can do this by transforming the data into a dimensional model with conformed Dimension tables. 

If you can invest time into creating a dimensional model, life usually becomes much, much easier. This includes writing DAX, supporting the model, performance tuning the model, applying filters. An example would be someone asking  can you filter this graph to show just   customer Y, for X location, between these dates. With a dimensional model, this is all possible. 

Here is my take on what the model might look like: 

1. Fact Tables:
   • FactInvoices: Contains invoice-related measures and foreign keys to dimensions.
   • FactDeliveries: Contains delivery-related measures and foreign keys to dimensions.
2. Dimension Tables:
   • DimDate: Contains date-related attributes for time-based analysis.
   • DimCustomer: Stores customer information.
   • DimLocation: Holds location data.
   • DimItem: Contains item-related information.
3. Relationships:
   • Both fact tables (FactInvoices and FactDeliveries) have relationships with all dimension tables.
   • The relationships are many-to-one from the fact tables to the dimension tables, represented by the crow's foot notation on the fact table side.

This model allows for flexible analysis across both invoices and deliveries, using shared dimensions. You can also easily extend this model by adding more attributes to the dimension tables or including additional fact tables as needed.

To implement this in Power BI:

1. Create separate tables for each entity in the diagram.
2. Establish relationships between the tables in the Power BI model, matching the relationships shown in the ERD.
3. Create measures in the fact tables for aggregations (e.g., sum of LineTonnage).
4. Use the dimension tables for filtering and grouping in your visualizations.

This structure will allow you to create the area chart showing total tonnage per month, as well as many other types of analyses, while maintaining a clear separation between facts and dimensions.

My favourite quote on this subject comes from the King of DAX - Marco Russo : "

“Honestly, it would be better to be a good data modeler and a mediocre DAX author than a DAX guru but a poor data modeler”

[sangosteve]

Thank you very much for the insight I seem to be close now. I managed to create the following:
Fact Tables:
Invoices
Deliveries
Dimension Tables:
Date
Items
Locations
Customers

I set up the relations between the FACTS and DIM tables as illustrated on the attached image. In addition I also have the global table  with the Total Tonnage Measure adding Tonnage from the Deliveries and Invoices Table.Table RelationsWrong Tonnage For This Location



My challenge now is Im only getting correct figures for one location. For other locations it only picks tonnage for the first day of that month. See attached image.

[danextian]

Hi @sangosteve ,

Are the dates in your dates table in sequence? No skips? I would add the dates from the dates table, dates from the fact table and the tonnage measure into a table visual to investiage. If the relationhips are setup correctly, you  should be seeing the dates from both columns next to each other except for the dates that are not in fact.

[sangosteve]

What I have done now to investigate is to delete all relations. Im now visualizing each table on its own and apparently, for deliveries Im only getting the ones for 36Retail. Its as if there is a filter when none is applied (See attached image). What coud be the cause for this because when I check in the Query Editor all deliveries are being loaded. See attached images for ref.Deliveries VisualizationDeliveries Query Editor

[sangosteve]

I finally figured out. I had hidden slicers that I put days ago😞. Your solution was working all along!!! Thank you so much.

[sangosteve]

Thank you I see that both solutions are suggesting Fact and Dimension tables let me try that. Thank you for shading light

[danextian]

hi  @sangosteve 

This is what I would do:

Create a separate dates table that encompassing the dates from both table either in M or DAX (you ca n use CALENDAR DAX function) and the date from this table to the dates to the fact tables usign a one-to-many single direction cardinality. Use this date in the visuals and not the other dates.

Create a dimensions table for the location and relate this to the fact tables as in the previous bullet. Use this in the visuals

DISTINCT (
    UNION (
        SELECTCOLUMNS ( TBL1, "Location", TBL1[Location] ),
        SELECTCOLUMNS ( TBL2, "Location", TBL1[Location] )
    )
)

Tonnage Measure =  SUM ( TBL1[Tonnage] ) + SUM ( TBL2[Tonnage] )