Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User
Super User

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors