Reply
Highlighted
Frequent Visitor
Posts: 5
Registered: ‎10-02-2018
Accepted Solution

How to use 2 different data sources to produce 1 graph?

Hi PowerBI People!

 

Some help needed please as I am going around in circles and reading so much on here I am losing the plot!

 

I have 2 data sources linked to one report - one showing all members who joined us over the past year, the other showing those who have left us over the year

 

I want to show number of joiners and the number of leavers in one line graph.  Doesnt sound to difficult to me, but it simply wont play ball!

 

Now i appreciate this may be a quick fix for you lot, so feel free to point and laugh, but I cant see it for looking as my graph shows the monthly totals for one field [great!] but then the final total for the year for the other field [not so great!]

 

images show what i mean.

 

help.  thank you.  You are great!Q1.PNGMy problem - note the thick line at top - this shows the year end total not the monthQ2.PNGFields used - although the date fied is different, i am using the month from eachQ3.PNGGraph 1 - by monthQ4.PNGGraph 2 - by month


Accepted Solutions
Community Support Team
Posts: 1,613
Registered: ‎07-30-2018

Re: How to use 2 different data sources to produce 1 graph?

hi, @Taff4Ever

There is a simple way for you to refer to:

Step1:

Use MONTH Function to create a Month column for these two data sources

Step2:

Create a month fact table

2.PNG

Step3:

Create the relationship between them by Month column

3.PNG

Step4:

Drag the field month form Month table into Axis

4.PNG

 

here is pbix, please try it.

https://www.dropbox.com/s/2zb3kbyoson4ym6/How%20to%20use%202%20different%20data%20sources%20to%20pro...

 

Best Regards,

Lin

 

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

View solution in original post


All Replies
Member
Posts: 76
Registered: ‎05-30-2018

Re: How to use 2 different data sources to produce 1 graph?

[ Edited ]

My guess is that you are confusing the visual by putting two unrelated timelines on the x-axis. (Lapsed Month and Date of Joining Month). It's only slicing the data by one of them and ignoring the other.

Member
Posts: 60
Registered: ‎03-23-2018

Re: How to use 2 different data sources to produce 1 graph?

Hi ,

 

Create one Date dimension table.

link staff data on joining date with date table

link leavers data on leaving date with date table.

 

create 2 measures

joiners = countdistinct(staff table[emp id])

leavers = countdistinct(leavers[empl id])

 

Use 'Month' from Date table as axis and above 2 measure to plot the chart.

leavers.JPG

 

 

Hope this helps,

SS

Community Support Team
Posts: 1,613
Registered: ‎07-30-2018

Re: How to use 2 different data sources to produce 1 graph?

hi, @Taff4Ever

There is a simple way for you to refer to:

Step1:

Use MONTH Function to create a Month column for these two data sources

Step2:

Create a month fact table

2.PNG

Step3:

Create the relationship between them by Month column

3.PNG

Step4:

Drag the field month form Month table into Axis

4.PNG

 

here is pbix, please try it.

https://www.dropbox.com/s/2zb3kbyoson4ym6/How%20to%20use%202%20different%20data%20sources%20to%20pro...

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Posts: 4,103
Registered: ‎01-14-2017

Re: How to use 2 different data sources to produce 1 graph?

Hi,

 

Share the link from where i can download your PBI file.