Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!My problem - note the thick line at top - this shows the year end total not the monthMy problem - note the thick line at top - this shows the year end total not the monthFields used - although the date fied is different, i am using the month from eachFields used - although the date fied is different, i am using the month from eachGraph 1 - by monthGraph 1 - by monthGraph 2 - by monthGraph 2 - by month

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi, @Anonymous

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%20produce%201%20graph.pbix?dl=0

 

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

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/
v-lili6-msft
Community Support
Community Support

hi, @Anonymous

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%20produce%201%20graph.pbix?dl=0

 

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.
Anonymous
Not applicable

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

BobBI
Resolver III
Resolver III

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.