Forum Discussion
Creating a line and clustered column chart with 2 different data sources that cannot be merged
Hi!
Great that you shared an example file!
Your model needs a shared year column so that the different data sets "get" that the years that they refer to are the same. So when you use the year column from one table it works with that table, but it does not "talk" with the year column in the other table (instead just returning blanks).
One good practice is to have a common date table, that you create relationships to and then use as the dimension in visuals: https://www.datacamp.com/tutorial/how-to-create-date-tables-in-power-bi-tutorial
I modified your model by adding a date table (although only with years for simplicities sake) that I created relationships to your other two fact tables with. (I also removed the "combined" table as it should not be needed with the right relationships in a model):
Then I modified your clustered column visual only by replacing the x-axis to using the Year column from the date table instead, and got the following:
Maybe this looks more like what you expected.Hope this helps!
Solution SageHi!
Great that you shared an example file!
Your model needs a shared year column so that the different data sets "get" that the years that they refer to are the same. So when you use the year column from one table it works with that table, but it does not "talk" with the year column in the other table (instead just returning blanks).
One good practice is to have a common date table, that you create relationships to and then use as the dimension in visuals: https://www.datacamp.com/tutorial/how-to-create-date-tables-in-power-bi-tutorial
I modified your model by adding a date table (although only with years for simplicities sake) that I created relationships to your other two fact tables with. (I also removed the "combined" table as it should not be needed with the right relationships in a model):
Then I modified your clustered column visual only by replacing the x-axis to using the Year column from the date table instead, and got the following:
Maybe this looks more like what you expected.
Hope this helps!
Thank you! This works perfectly.
I created a table manually via the enter table section of the 'home' tab. For my learning, what should the DAX expression be if I would only like to create a datable of years (2008, 2013, 2018) via DAX?
- TomasAndersson
Great to hear!
Here's a few examples:- If you are completely sure you will only ever need years and not months, days etc you could just write:
Years = {2010,2011,2012} //.. etc- The function CALENDAR() is otherwise a good choice. It creates dates between a start and end date you set.
Dates = CALENDAR("1/1/2008","12/31/2018")To get years, you could then add a calculated column:
Year = year(Dates[Date])- Not DAX, but you could also load an external source, such as an Excel file, with dates that you created there
