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?
Factappointments table 2
Error message when using lookupvalue: