Hi all,
I have a branched organization, where each employee may work in more than one branch.
I have a table that detail the tasks performed by each employee. Let's assume the following structure for simplicity:
EmpID | Branch | Tasks |
111 | A | 5 |
111 | B | 7 |
222 | A | 3 |
222 | C | 10 |
My dashboard analyses the status of a branch, so it all depends on a single branch slicer.
I want to add a map visual that would show other branches that share the same employees and the number of tasks they performed there (branch lon+lat is known and constant).
For the above example, the map should show branch B with bubble size 7 and branch C with bubble size 10.
How can I do that, while keeping the branch slicer?
Thanks in advance,
Aviram
Solved! Go to Solution.
Based on the solution by @Mahesh0016 , I tweeked it a bit to do exactly what I wanted. Sharing here for community use:
1. Create a disconnected branch table
2. In that table, add a measure that checks if the branch was selected:
@AviramWeiss
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Thank you for your reply, @Mahesh0016 .
I appologize, but I don't know how to upload a pbix here (complete newbie...).
Here are 2 sample tables that I used:
1. Branches:
Branch | Lat | Lon |
A | 32.4 | 35.4 |
B | 32.5 | 35.3 |
C | 32.4 | 35.3 |
D | 32.5 | 35.4 |
2. Employee Tasks:
EmpID | Branch | Month | Year | Tasks |
111 | A | 6 | 2022 | 10 |
111 | A | 7 | 2022 | 15 |
111 | B | 7 | 2022 | 12 |
111 | B | 8 | 2022 | 5 |
111 | C | 9 | 2022 | 14 |
222 | A | 6 | 2022 | 4 |
222 | D | 6 | 2022 | 13 |
222 | A | 7 | 2022 | 16 |
333 | B | 6 | 2022 | 14 |
Picking Branch A in a slicer, I want to show one table with the amount of tasks performed in this branch (that's easy):
EmpID | Tasks |
111 | 25 |
222 | 20 |
Then, based on the previous table, I know that only employees 111 and 222 worked in branch A. Thus, I want to show the tasks they performed in all other branches (ie exclude tasks performed in branch A and exclude tasks performed by employee 333, who doesn't work in branch A):
EmpID | Branch | Month | Year | Tasks |
111 | B | 7 | 2022 | 12 |
111 | B | 8 | 2022 | 5 |
111 | C | 9 | 2022 | 14 |
222 | D | 6 | 2022 | 13 |
Hope it is a bit clearer now.
Thanks again,
Aviram
@AviramWeiss
1.Create distinct Branch table.
**If this post helps, please consider accept as solution to help other members find it more quickly.
Thank you very much, @Mahesh0016 .
I'm not sure I understood your solution.
I assume there should be a new branch table, disconnected from the old one. I created it, and called it "Disconnected_Branches".
Then I created IsExcluded as a cloumn in table EmpTasks. Was that the right way???
Then created SalesAmount as a measure in the new table Disconnected_Branches. Right???
I received an empty list.
Please fix my mistakes and excuse my inexperience.
Thank you,
Aviram
>>Here is my model please follow this.
>>Then I created IsExcluded as a cloumn in table EmpTasks. Was that the right way??? --> No, you should Create measure not column.
>> Then created SalesAmount as a measure in the new table Disconnected_Branches. Right??? ---> ohk it is fine .
** I hope this helps.
Hi again,
I'm sorry to be a nudge, be it still doesn't work.
I created the disconnected table and 2 measures as described. My ERD now looks like this:
Yet, when I try to present the tasks performed in other branches by the same employees, I receive a list of all branches, and the sum of tasks that were performed in the selected branch:
What I really want is this:
It was mannually achieved by filtering Branch<>"A" and EmpID in (111,222), but it has to be dynamic, of course...
Based on the solution by @Mahesh0016 , I tweeked it a bit to do exactly what I wanted. Sharing here for community use:
1. Create a disconnected branch table
2. In that table, add a measure that checks if the branch was selected:
User | Count |
---|---|
74 | |
37 | |
33 | |
16 | |
13 |
User | Count |
---|---|
83 | |
30 | |
26 | |
16 | |
13 |