cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Other branches with the same employees

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?

Aviram

1 ACCEPTED SOLUTION
Helper I

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:

SelectedBranch = if(max([Branch]) in ALLSELECTED(Branches[Branch]),1,0)
3. In the Employees table, add a measure that checks if the employee works in the selected branch:
4. Back in the disconnected branches table, create a third measure, based on the previous 2:
VAR filter1 = FILTER ('Employees',[SelectedEmp] >0)
var filterEmp = filter(all('EmpTasks'[EmpID]),[EmpID] in filter1)
RETURN
CALCULATE (
filterEmp, filterBranch
)

(The bolded row is the actual calculation. It can be replaced with distinct count of employees or any other calculation).

Thank you very much, @Mahesh0016 , for the patience and the professional solution. Wouldn't have worked without you.
7 REPLIES 7
Super User

@AviramWeiss
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Helper I

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

 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

Super User

@AviramWeiss
1.Create distinct Branch table.

isExclude =
IF(
MAX(Branches[Branch]) IN
ALLSELECTED(Disconnected_Branches[Branch])
&& COUNTROWS(ALLSELECTED(Disconnected_Branches[Branch])) <>
COUNTROWS(ALL('Disconnected_Branches')),
1,
0
)
*****************************************
Sales Amount (Disconected) =
VAR FilterRegions =
FILTER (
[isExclude] =0
)
RETURN
CALCULATE (
FilterRegions
)

**If this post helps, please consider accept as solution to help other members find it more quickly.

Helper I

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???

Please fix my mistakes and excuse my inexperience.

Thank you,

Aviram

Super User

>>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.

Helper I

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...

Helper I

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:

SelectedBranch = if(max([Branch]) in ALLSELECTED(Branches[Branch]),1,0)
3. In the Employees table, add a measure that checks if the employee works in the selected branch:
4. Back in the disconnected branches table, create a third measure, based on the previous 2:
VAR filter1 = FILTER ('Employees',[SelectedEmp] >0)
var filterEmp = filter(all('EmpTasks'[EmpID]),[EmpID] in filter1)
RETURN
CALCULATE (
filterEmp, filterBranch
)

(The bolded row is the actual calculation. It can be replaced with distinct count of employees or any other calculation).

Thank you very much, @Mahesh0016 , for the patience and the professional solution. Wouldn't have worked without you.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors