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
rdops
Helper III
Helper III

Visualise two different data sets in one chart

Hi all,

 I am totally new to PBI, have been exploring it for approx 1 month now and still have very limited knowledge.

I hit a wall while trying to combine/visualise  two sets of data from different data sources in one single chart.

What I am trying to achieve: compare the total hours of leave entered in two separate systems. 

Problem is, the only common element between these two data sources is the employee ID. I can get the total hours discrepancy between the two systems but I cannot separate them by month. Both data sources have months but when I pick 'month' as a slicer, one of the data total doesnt show properly. I guess because the 'month' reference is not common to both of them.

Is there a way to resolve this? Is really important that I see (on a monthly basis) the discrepancy between hours entered in both systems. 

Thanks in advance,

Rdops.

 

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

Hi @rdops 

Is this problem sloved? 
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
 
Best Regards
Maggie

Hi Maggie, 

It didnt work for me. Not sure if I did something wrong but even after following all these steps I still couldnt see the two sets of data separately. I ended up rebuilding the report and created a new column with a lookvalue formula to match the employees names, but for some reason, even with the employees being consistent across both systems, data from only one systems gets updated when I select the month. I guess you can close the case. I will play around with the dates/months now and if I cant find a solution I will open another thread specific to dates.

 

 

v-juanli-msft
Community Support
Community Support

Hi Maggie,

Thanks for your reply. See data below.

The green data is the leave hours entered on system A, red data is leave entered in system B. Each system is a separate Excel sheet and they should all have the relevant relationships. Eg, Employee (on system A) and Worker (on system B) are connected.

Months are also connected on both systems.

 

If you look at the chart with two bars (191.2/171.25), this is the total leave on each system, side by side in one single chart, like I wanted. However, when I filter by 'Worker', and have the chat by month, only one data set (red) displays properly. The other data shows the exact same number across the months.

Note that some people entered leave on system A but didnt enter on System B, this is why I need this report, to compare what is showing in one system and not the other. This is the ultimate result I want, to have both data side by side, by month, and by employee. I found a way to visualise it, which does the work, but I am sure there is a way to do it properly. What I did, I  created two chiclets slicer for the months, one chiclet per system, so when I need to compare month by month, I select the worker and then click on the filter/month for system A, and filter/month for system B. This way I see them both, but on two distinct charts, as opposed to the same chat, side by side. Hope it makes sense? Month by month data (only one set of data gets updated)Month by month data (only one set of data gets updated)two filters to compare data by monthtwo filters to compare data by month

Hi @rdops 

One method is to append two tables in one table. 

In Edit queries,

add a custom column in "system a" table

system="system a"

add a custom column in "system b" table

system="system b"

Change the column name "worker" to  "employee" in  "system b" table

Append a new query, 

Uncheck "enable load" for "system a" table and "system b" table,

So finally, clseo&&apply, we see only "append" table to show on data model view.

Capture2.JPG

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

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.

Top Solution Authors
Top Kudoed Authors