Forum Discussion
Combining Data From Unrelated Tables To Create Charts
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:
- Fact Tables:
- FactInvoices: Contains invoice-related measures and foreign keys to dimensions.
- FactDeliveries: Contains delivery-related measures and foreign keys to dimensions.
- Dimension Tables:
- DimDate: Contains date-related attributes for time-based analysis.
- DimCustomer: Stores customer information.
- DimLocation: Holds location data.
- DimItem: Contains item-related information.
- 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:
- Create separate tables for each entity in the diagram.
- Establish relationships between the tables in the Power BI model, matching the relationships shown in the ERD.
- Create measures in the fact tables for aggregations (e.g., sum of LineTonnage).
- 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”
- Fact Tables:
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:
- Fact Tables:
- FactInvoices: Contains invoice-related measures and foreign keys to dimensions.
- FactDeliveries: Contains delivery-related measures and foreign keys to dimensions.
- Dimension Tables:
- DimDate: Contains date-related attributes for time-based analysis.
- DimCustomer: Stores customer information.
- DimLocation: Holds location data.
- DimItem: Contains item-related information.
- 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:
- Create separate tables for each entity in the diagram.
- Establish relationships between the tables in the Power BI model, matching the relationships shown in the ERD.
- Create measures in the fact tables for aggregations (e.g., sum of LineTonnage).
- 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”
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.
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.
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
I finally figured out. I had hidden slicers that I put days ago😞. Your solution was working all along!!! Thank you so much.
