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.

View solution in original post

5 REPLIES 5
Highlighted

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.

View solution in original post

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.

Taff4Ever Frequent Visitor
Frequent Visitor

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 63 members 2,569 guests
Please welcome our newest community members: