Slicer on active and inactive relation

[MaximeGinini]

Hello,

I have a table "CJI3" containing informations about valued time spent by employees.
It has several data but the ones I am focusing on right now is "cost center origin", "cost center responsible", "project", "euros".
I have another table wich is a table for regrouping cost centers into services, so it includes "cost center" "services".
So, "cost center origin" and "cost center responsible" are both linked to "cost center" in my data model, but obviously one is inactive which is "origin" in this case.

How this data work: an employee is attached to a cost center, and can work on multiple projects which are also attached to cost centers.
So, either the employee from cost center A works in a project attached to his department A, and then we both have A in origin and responsible.
But if for some reason, he works for a project attached to another department B, then responsible is B and origin is A.
Origin is the cost center attached to the employee
Responsible is the cost center attached to the project
For cost center A, I need to analyse what has been worked from A guys to other departments B, C etc, minus what has been worked on department A from guys on department B, C etc.


What I am trying to do is:
- create a slicer "services" => Done

- create a table of projects with two measure: time sent, time received

My problem:
If I select any service in my slicer, the table CJI3 will be filtered by the active relation, selecting only the responsible cost centers.
I only got projects from responsible departments then, but not the projects the service could have been working on and for which it isn't responsible.
I figured how to diplay the right data though, I can get the 150 and 825 displayed easily, but when I put some detail it doesnt work anymore.

In red: 1018 + 791 = 1809 time sent
In yellow: 754 + 409 = 1163 time received

Many thanks for your help and hope I have been clear enough, otherwise I will answer any question you may have, and hope my english is good enough for you to understand

Raw data if you wanna try with this sample

				BY LINKS
CC Responsible	CC Origin	Project	Euro	Service Responsible	Service Origine		CC	Service
A0010	A0010	A	230	DEV	DEV		A0010	DEV
A0010	A0020	A	500	DEV	CORP		A0020	CORP
A0010	A0030	B	325	DEV	AOS		A0030	AOS
A0020	A0020	C	100	CORP	CORP
A0020	A0010	C	150	CORP	DEV
	SLICER SELECTION = DEV
What I want to see
Projects	Time sent	Time received	Diff
A		500	500
B		325	325
C	150		-150
total	150	825	675

[v-yajiewan-msft · Accepted solution]

Hi @MaximeGinini , hello jordandouglas, thank you for your prompt reply!

Please use the following measures to meet your requirements:

TimeSent = 
CALCULATE (
    SUM(CJI3[Euro]),
    USERELATIONSHIP('CJI3'[CC Origin], 'Cost Center'[Cost Center]),  
    CJI3[Service Origin] = SELECTEDVALUE('Cost Center'[Service]),  
    CJI3[Service Responsible] <> SELECTEDVALUE('Cost Center'[Service])  
)

TimeReceived = 
CALCULATE (
    SUM(CJI3[Euro]),
    CJI3[Service Responsible] = SELECTEDVALUE('Cost Center'[Service]),  
    CJI3[Service Origin] <> SELECTEDVALUE('Cost Center'[Service]) 
)

Difference = [TimeReceived] - [TimeSent]

Result:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jordandouglas]

If you would like to slice on either service responsible or service origin using only one slicer then this has been achieved before.

https://community.fabric.microsoft.com/t5/Desktop/Single-Slicer-to-Filter-Multiple-Columns/m-p/34232...