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.
Hi,
I am trying to come up with a VLookup style measure that will count how many records there are for the "Queue" field from one table against the "Customer" field in the other table. The two tables cannot be related because none of the fields have a "1-to-Many" or "1-to-1" type field that can be related.
Just to give you an idea where this is coming from, the data is from a helpdesk, and one table is the inbound phone call data, and the other is ticketing system data. I want to put the ticket data stats in the same visual with the phone stats.
I know there are a lot of threads about this kind of thing, but I really could not find anything that I could apply to this situation. Any help would be appreciated!
Thanks,
Scott
Solved! Go to Solution.
Hi @ScottR-MT
Seems you are using a MEASURE instead of a calculated column
I wrote a calculated column formula
As a MEASURE in PHONE data, you can use this
= CALCULATE ( COUNTROWS ( TicketData ), FILTER ( ALL ( TicketData ), TicketData[Customer] = SELECTEDVALUE ( PhoneData[Queue] ) ) )
isn;t in this case queue and customer will have the same value, why relation cannot be set on this? May be I didn;t understood your question, if you post sample data with expected output, it will help.
You can create calculated tables to make unique queue/customer and link from there.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
It seems as though Power BI will not create a relationship between tables if the columns you are trying to relate both have multiples of the same value. To clear it up, the phone data has many phone calls from the same ~20 customers, and the same goes for the ticketing data; many tickets from the same ~20 customers, etc. Below is data from Excel files with anonymized sample data so you can see what the data looks like. Note the Queue column in the Phone Data and the Customer column in the Ticket Data.
Phone Data
Period | Day of Week | Media | Group | Queue | Transaction | Create time | Accept time | Time to Accept | Total time processing transaction |
10/16/2017 | Mon | Phone | Service Desk | Customer 1 | 88827 | 10/16/2017 7:54 | 10/16/2017 7:54 | 00:17 | 0:03:28 |
10/16/2017 | Mon | Phone | Service Desk | Customer 2 | 88925 | 10/16/2017 8:30 | 10/16/2017 8:30 | 00:15 | 0:06:07 |
10/16/2017 | Mon | Phone | Service Desk | Customer 2 | 92444 | 10/16/2017 15:17 | 10/16/2017 15:17 | 00:08 | 0:20:11 |
10/17/2017 | Tue | Phone | Service Desk | Customer 1 | 95127 | 10/17/2017 8:10 | 10/17/2017 8:10 | 00:12 | 0:02:51 |
10/17/2017 | Tue | Phone | Service Desk | Customer 2 | 95486 | 10/17/2017 9:31 | 10/17/2017 9:32 | 00:27 | 0:15:43 |
10/17/2017 | Tue | Phone | Service Desk | Customer 1 | 95558 | 10/17/2017 9:42 |
|
|
|
10/17/2017 | Tue | Phone | Service Desk | Customer 2 | 97598 | 10/17/2017 13:52 | 10/17/2017 13:52 | 00:12 | 0:13:47 |
Ticket Data
Id | Customer | Title | Source | Status | Created Date | First Resp (min) | Escalated | Resolved Date | Resolution Category |
IR95253 | Customer 1 | USER ID PROBLEM | Console | Resolved | 11/28/2017 7:14:01 PM | 13 |
| 11/28/2017 7:30:40 PM | Networking Problems |
IR95262 | Customer 1 | CONNECTION ISSUE | Phone | Resolved | 11/28/2017 8:56:38 PM | 5 |
| 11/28/2017 9:02:21 PM | Networking Problems |
IR95263 | Customer 2 | Mobile Phone Issue | Active | 11/28/2017 8:58:23 PM | 4 | TRUE |
| Mobile | |
IR95277 | Customer 2 | Laptop | Active | 11/29/2017 11:33:20 AM |
|
|
| Hardware Problems | |
IR95280 | Customer 2 | Computer will not connect to network | Console | Resolved | 11/29/2017 11:56:29 AM | 13 | TRUE | 11/29/2017 4:00:09 PM | Hardware Problems |
IR95281 | Customer 2 | conference Bridge request. | Active | 11/29/2017 11:58:20 AM |
| TRUE |
| Phones | |
IR95294 | Customer 1 | AD Password reset | Phone | Resolved | 11/29/2017 6:33:18 PM |
|
| 11/29/2017 6:35:05 PM | Active Directory |
IR95296 | Customer 1 | AD Password reset | Phone | Resolved | 11/29/2017 6:35:45 PM |
|
| 11/29/2017 6:40:43 PM | Active Directory |
Hi @ScottR-MT
You can use this calculated Column in Tickets Data to count the rows in Phone Data related to same Customer
= CALCULATE ( COUNTROWS ( PhoneData ), FILTER ( ALL ( PhoneData ), TicketData[Customer] = PhoneData[Queue] ) )
OR You can use this calculated Column in Phone Data to count the rows in Tickets Data related to same Customer
= CALCULATE ( COUNTROWS ( TicketData ), FILTER ( ALL ( TicketData ), TicketData[Customer] = PhoneData[Queue] ) )
Thanks for the reply, but on the last part, it won't allow me to pick the Queue column. It seems to only allow me to use one of the numeric measures. I can't figure out why that is.
= CALCULATE ( COUNTROWS ( TicketData ), FILTER ( ALL ( TicketData ), TicketData[Customer] = PhoneData[Queue] )
Hi @ScottR-MT
Seems you are using a MEASURE instead of a calculated column
I wrote a calculated column formula
As a MEASURE in PHONE data, you can use this
= CALCULATE ( COUNTROWS ( TicketData ), FILTER ( ALL ( TicketData ), TicketData[Customer] = SELECTEDVALUE ( PhoneData[Queue] ) ) )
Thank you very much for your assistance! It worked, but I now have another problem. I have the Queues/Customers divided into groups, and there are subtotals for each group. Only problem now is that the subtotals for the Incidents column you helped me build display incorrectly, and it seems to adopt the total of rows in the tickets table with a blank customer. The bottom one doesn't display at all, which is actually correct for that group, and would also be correct for the top group as well. Below are two tables from separate visuals that will outline what I mean.
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 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |