Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
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
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
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
?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |