How to connect the same field to different tables that mean the same thing.

[johnjohnson]

 Hello all,

I'm currently finding some issues connecting the same field to different tables.

Basically I have 4 tables (amongst others but these are these problem)

Net Promoter Score (An NPS is atributted to an agent and there can be several NP scores by Cases)

Case Detail (Has a Case owner, which might/might not be the NPS agent)

Users table (which I want to connect to the Case Owner ID and NPS Owner ID)

My end goal:

Lets say I want to see the data of "John Johnson"

I want to have a filter with agent names on it. When the user chooses "John Johnson", I want it to show the Cases in which "John Johnson" is the owner and the NPSs in which "John Johnson" is also the owner. I dont want show cases where Case Owner is John Johnson" but the NPS is from "Anders Anderson".

Thanks in advance.

Cheers

[johnjohnson]

Hello,

first of all thank you for your help.

Here is a sample (datamodel is way bigger but this is the part that is helding me back).

	Users				NPS
Case ID	Case OwnerID	CreatedDate	Case Reason		ID	Name
1	1	01/01/2017	Data		1	John Jonhson
2	2	02/01/2017	Voice		2	Anders Anderson
3	2	03/01/2017	MMS		3	Dom Dominic
4	4	04/01/2017	SMS		4	Richard Richardson
5	5	05/01/2017	Handset issue		5	Eric Ericson
NPS
NPS ID	Case ID	CreatedDate	NPS Owner ID	NPS Value
1	1	01/01/2017	1	80
3	2	02/01/2017	2	60
5	3	03/01/2017	3	70
7	4	04/01/2017	4	15
9	5	05/01/2017	5	60

So basically:

A Case has a case owner.

A NPS has an NPS owner, that might or might not be the Case owner.

A case may have 0, 1 or >1 NPSs.

My goal is to do a User Page. That user page should show the Cases in which the user I filter is the owner and calculate the NPS for which that user is the owner.

If I connected USERID to the CaseOwnerID, it will calculate the NPS which is inside those Cases, that may not be from that Owner. If I connect the UserID to NPSOwnerID, it won't return me all Cases in which that User is the owner.

Thank you once again.

[v-ljerr-msft · Accepted solution]

Hi @johnjohnson,

In your scenario, you can firstly connected USERID to the CaseOwnerID, then use the formula below to create a measure to calculate the NPS for which the selected user is the owner. 

Measure =
CALCULATE (
    SUM ( NPS[NPS Value] ),
    FILTER ( ALL ( NPS ), NPS[NPS Owner ID] IN VALUES ( Users[ID] ) )
)

Regards

[johnjohnson]

Hello @v-ljerr-msft , that is exactly what I wanted!

Thank you very much.

[v-ljerr-msft]

Hi @johnjohnson,

Could you just post your table structures with some sample/mock data, and the expected against the data? So that we can better assist on this issue. 

Regards

[soundwave123]

Would it be possible for you to supply what the four tables columns are in addition to some example data?

Example below:

Table1

NPS column

1

2

3

4

5

Table2

Case Detail column

Bob J.

Stephanie P.

Larry M.

Table3

Users Table Column

?