cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Taff4Ever Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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.
4 REPLIES 4

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

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.

BobBI Member
Member

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
Community Support Team

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
Super User

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

Hi,

 

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