cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AviramWeiss
Helper I
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   
111A5
111B7
222A3
222C10

 

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

1 ACCEPTED 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:

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:
SelectedEmp = calculate(sum(EmpTasks[Tasks]),EmpTasks[EmpID]=max('Employees'[EmpID]))
4. Back in the disconnected branches table, create a third measure, based on the previous 2:
Other branch tasks =
VAR filter1 = FILTER ('Employees',[SelectedEmp] >0)
var filterEmp = filter(all('EmpTasks'[EmpID]),[EmpID] in filter1)
var filterBranch=filter(all('EmpTasks'),'EmpTasks'[Branch]=max('Disconnected_Branches'[Branch]))
RETURN
    CALCULATE (
        SUM('EmpTasks'[Tasks]),REMOVEFILTERS(Branches[Branch]),
        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.

View solution in original post

7 REPLIES 7
Mahesh0016
Solution Sage
Solution Sage

@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:

BranchLatLon
A32.435.4
B32.535.3
C32.435.3
D32.535.4

 

2. Employee Tasks:

EmpIDBranchMonthYearTasks
111A6202210
111A7202215
111B7202212
111B820225
111C9202214
222A620224
222D6202213
222A7202216
333B6202214

 

Picking Branch A in a slicer, I want to show one table with the amount of tasks performed in this branch (that's easy):

EmpIDTasks
11125
22220

 

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):

EmpIDBranchMonthYearTasks
111B7202212
111B820225
111C9202214
222D6202213

 

Hope it is a bit clearer now.

 

Thanks again,

Aviram

@AviramWeiss 
1.Create distinct Branch table.

Mahesh0016_0-1672141369533.png

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 (
        'Employee Task',
        [isExclude] =0
    )
RETURN
    CALCULATE (
        SUM('Employee Task'[Tasks]),
        FilterRegions
    )
 
Mahesh0016_1-1672141502377.png

 

 

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

@AviramWeiss 

Mahesh0016_0-1672233024777.png

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

AviramWeiss_0-1672297629509.png

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:

AviramWeiss_1-1672297793391.png

 

What I really want is this:

AviramWeiss_2-1672298071023.png

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:

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:
SelectedEmp = calculate(sum(EmpTasks[Tasks]),EmpTasks[EmpID]=max('Employees'[EmpID]))
4. Back in the disconnected branches table, create a third measure, based on the previous 2:
Other branch tasks =
VAR filter1 = FILTER ('Employees',[SelectedEmp] >0)
var filterEmp = filter(all('EmpTasks'[EmpID]),[EmpID] in filter1)
var filterBranch=filter(all('EmpTasks'),'EmpTasks'[Branch]=max('Disconnected_Branches'[Branch]))
RETURN
    CALCULATE (
        SUM('EmpTasks'[Tasks]),REMOVEFILTERS(Branches[Branch]),
        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.

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors