Pulling values from one table to another

DAX DAX Calculations
1

I am working with a data model with 4 tables, each representing a step a company’s sales funnel.

One fact table is for leads. Some of those leads schedule appointments, and are added to a second table with a list of appointments. Some of those leads with appointments then purchase a product or service, and are then added to either the factproducts table or factservices table.

Here’s a summary of the tables:

(1) Dimleads has the list of leads, each with a unique ID in the [Abbreviated ID] column. There’s one row per lead.
(2) FactAppointments contains a list of appointments scheduled with leads from the Dimleads table, one row per appointment. Factappointments has 2 ID columns, one [LeadID] which matches the [Abbreviated ID] from the Dimleads table. The other ID is [appointmentID], which is assigned to each lead an ID for their appointment.
(3) Factproducts is a transaction table, with one row per product purchase. Each transaction has an [appointmentID] which matches that from the Factappointments table
(4) Factservices is another transaction table, with one row per service purchase. Each transaction also has an [appointmentID] which matches that from the Factappointments table.

The Factappointments table contains both a [LeadID] and [Appointment ID]. I need to pull the appointment ID from the Factappointments table and match it with the [Abbreviated ID] on the dimleads table. Basically I need to create a calculated column in the dimleads table which contains the [appointment ID] from Factappointments.

I also need to pull the name of the product and service from the product and service table over to the dimleads table. This should be possible since each the factproduct table and factservice table both contain an [appointment ID] matching that on the Factappointments table.

I am doing this because I want to be able to filter the list of leads in the dimleads table by the type of product and service they purchase. I also want to be able to track each lead through the sales funnel using the appointment ID as the shared ID on every table.

I’m not sure how to pull the appointment ID from one table to the other and so I’m looking for help writing the calculated column DAX to accomplish this.

I also tried to use the LOOKUPVALUE function, which I thought would look up the factappointments[ID] and add that ID to the dimleads table, using the shared ID between the tables (FactAppointments[LeadID] and dimleads[Abbreviated ID]. However this function returns an error that “a table of multiple values was supplied where a single value was expected.” Perhaps this is because there are duplicate [Abbreviated ID]'s in the dimleads table.

Here’s the dax I tried:

RetrieveAppointmentID = LOOKUPVALUE(FactAppointments[ID], FactAppointments[LeadID], dimleads[Abbreviated ID])

Thanks for your help. @harsh any ideas?

Some images:

Dimleads Table:

ID column from dimleads table
ID column from dimleads table2133×808 190 KB

Factappointments table

ID column from factappointments table
ID column from factappointments table2097×1052 513 KB

Factappointments table 2

LeadID column from factappointments table
LeadID column from factappointments table951×1006 108 KB

Error message when using lookupvalue:

error message
error message1936×1078 226 KB

2

@pete.langlois,

Rather than doing this via calculated columns and LOOKUPVALUES, what about doing it via merge in Power Query? Gives you a lot more flexibility, as well as better performance.

Would you be able to provide a sample PBIX with the data anonymized as needed? That would help us provide you a very specific solution here.

  • Brian
3

I was able to find the solution in the powerbi community forum here:

There were duplicate Lead ID’s, so lookupvalue was trying to return multiple matching appointmend ID’s, which it couldn’t do in a single row.

This dax returned only the first matching value, and thus automatically filtered out duplicates, which for my case is fine:

Retrieved Appointment ID =
CALCULATE (
FIRSTNONBLANK(factappointments[id], 1 ),
FILTER ( ALL (FactAppointments), FactAppointments[LeadID] = DimLeads[ID Abbreviated] ) )