cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Taff4Ever Helper I
Helper I

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

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

5 REPLIES 5
Anonymous
Not applicable

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 Resolver III
Resolver III

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

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

Highlighted
Super User IV
Super User IV

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Taff4Ever Helper I
Helper I

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

Hello again, thank you for this fix, I use it a lot and it is a great help, but every so often i lose track of the sorting option - it wont sort into MONTH.NAME order and i struggle to replicate this even copying the file you send through!?

How do i ensure that the month name is in date order and not alphabetical order?

Thank you

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors