Forum Discussion
Combining data from two tables
I think I've fixed it by creating a separate table that has the MonthName and MonthCode in it. Then created relationships with the tables containing MonthName.
Do you have a Date dim table? Ideally you need a star schema model where you have fact and dimension tables. Your two fact tables with dates of transactions/occurrences/etc. would each have a join to your date table and the date for the chart would come from the dim table.
http://powerbiwithme.com/2023/07/30/the-star-schema-edition/
http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/
Thanks Audrey. I'm afraid I'm a bit of a newbie when it comes to Power BI and databases in general, so I've put my pbix file on OneDrive:
The strange thing is that the Card in the bottom right-hand corner is accurate if the whole period of 6th Sept 2023 to 22nd Jan 2024 is selected (for eg, if you select Adno 3732, the Card correctly reads 16.32% for the whole period of 6th Sept 2023 to 22nd Jan 2024). When I vary the date range using the date slider, the Card is no longer accurate (for Adno 3732, it should read 9.41% for Sept, 11.24% for Oct, 26.97% for Nov, 10.61% for Dec and 25.49% for Jan).
The top graph and the middle one are both correct too. However, the bottom graph (which is the one I'm trying to sort out) is a complete work of fiction!
I'm very confused!
I think I've fixed it by creating a separate table that has the MonthName and MonthCode in it. Then created relationships with the tables containing MonthName.