Dates Field from Different Data Sources Not Separating

[benjamincoady]

Hi, 

I am trying to bring in 2 different datasets, they are similar but have their differences. I am trying to create a bar chart showing the sum of "Happy Scores" against the date when the survey was taken. When I try to build the visual, PowerBI keeps putting them together and stacking the values even though they have two different dates. One survey was conducted on 1/04/19 and the other 1/07/18, both with different totals scores which is what I'm trying to show. It does this no matter was visual I use. 

Attached are 3 images; 1. showing the visual, 2. showing one data source with dates and 3. showing other data sources with dates

Any idea?

Thanks in advance!! 

[benjamincoady · Accepted solution]

All sorted, needed to brush up on my Star Schema and create primary keys between the data tables. For anyone looking to work with data sets within the same dashboard, Ralph Kimballs Dimensional Modelling Techniques book is fantastic and goes through this topic comprehensively. 

Thanks for everyones reply! 

[chawalit]

I think you should another date table contain all date from both tables. And make a relation from the date column in date table to date column in both tables. So, on the viz you use the date column in date table to axis field and two measures to values field on viz.

Hope this help.

[dax]

Hi benjamincoady,

In your scenario, I guess that you want to use measures from two tables in the same chart, but I don't know what style you want to show in x-axis? So if possible, could you please inform me in details?

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[benjamincoady]

Anyone?

[dax]

Hi benjamincoady, 

If you want to use two measure from different table,  I think you need to use same date axis(you could create a calendar table, then use this date in axis), which should work.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[benjamincoady]

Hi Zoe, 

That's correct, yes. I'd like to show measures from two tables in one graph chart. 

On the X-axis, I'd like the date when the survey was completed, there is one date only for each table. I am trying to show 2 dates, therefore creating two seperate columns, not one conjoined column which it is doing at the moment. 

Any idea?

Thanks

[benjamincoady]

Additional photos below: