cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ScottR-MT
Helper I
Helper I

Measure to lookup criteria from two unrelated tables

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

1 ACCEPTED 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] )
    )
)

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

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. 

 






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

Email

Active

11/28/2017 8:58:23 PM

4

TRUE

 

Mobile

IR95277

Customer 2

Laptop

Email

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.

Email

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] )
)

 


Regards
Zubair

Please try my custom visuals

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] )
    )
)

 

 


Regards
Zubair

Please try my custom visuals

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.

 

Table I am working on with phone and ticket valuesTable I am working on with phone and ticket valuesTable to confirm ticket valuesTable to confirm ticket values

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.