Creating a line and clustered column chart with 2 different data sources that cannot be merged

[angelsmilexd]

Hi, I'm still new to Power BI and am trying to start my own data visualization project. 

I have 2 data sources - one on the general resident population of my country and one on the flat (HDB) resident population. I wanted to show the changes in these populations in the same line and clustered column chart across 2008, 2013 and 2018. Ideally, this should show how the flat resident population changes with the general resident population. 

I have filtered to these 3 time periods for both datasets and created measures for each dataset to calculate the population. However, my chart keeps showing a blank (year) period and straight lines for the flat resident population. 

When I tried to create separate charts, the measures work.

Will anyone be able to advise? Attaching my power BI draft here.

https://drive.google.com/file/d/1ZbJ2e0_tMm4_XFyXWCI6vtbcqsE7rzQR/view?usp=sharing

Thank you!

[TomasAndersson · Accepted solution]

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!

[angelsmilexd]

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