Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Hi @rdops
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.
Hi @rdops
Could you show me some sample data?
Maybe we need to make some transformations for the two data model.
Here are some similar threads:
https://community.powerbi.com/t5/Desktop/Compare-two-sets-of-data/td-p/18538
https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data
Best Regards
Maggie
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?
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.