topic Other branches with the same employees in DAX Commands and Tips

Other branches with the same employees

AviramWeiss — Mon, 26 Dec 2022 14:52:39 GMT

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

Re: Other branches with the same employees

Mahesh0016 — Tue, 27 Dec 2022 05:46:48 GMT

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

Re: Other branches with the same employees

AviramWeiss — Tue, 27 Dec 2022 10:06:39 GMT

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

Re: Other branches with the same employees

Mahesh0016 — Tue, 27 Dec 2022 11:45:39 GMT

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')),

)
*****************************************

Sales Amount (Disconected) =

VAR FilterRegions =

FILTER (

'Employee Task',

[isExclude] =0

)

RETURN

CALCULATE (

SUM('Employee Task'[Tasks]),

FilterRegions

)

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

Re: Other branches with the same employees

AviramWeiss — Tue, 27 Dec 2022 14:06:06 GMT

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

Re: Other branches with the same employees

Mahesh0016 — Wed, 28 Dec 2022 13:14:23 GMT

AviramWeiss

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

Re: Other branches with the same employees

AviramWeiss — Thu, 29 Dec 2022 07:16:44 GMT

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

Re: Other branches with the same employees

AviramWeiss — Thu, 29 Dec 2022 15:10:18 GMT

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.