Look up values from two different table basis filter conditions

[minaxi]

Hi,

I need to build a report where values in the table is the lookup values from another table.

Lets say I have 4 different table: Campaign, Campaign Member, Contact and Lead.

Campaign Table
Campaign ID	Campaign Name	Campaign Member ID
123	abc	12345
123	abc	1234567
345	def	345
57	ghk	222
12	kfjd	566

Campaign Member Table
campaign Member ID	Type	Lead ID	ContactID
12345	Lead	1800	null
1234567	Lead	1900	null
345	Contact	null	2000
222	Contact	null	1234
566	Contact	null	8900

Lead Table

LeadID	First Name	LastName	Email
1800	john	v	johnv@abc.com
1900	marry	Josheph	marryj@yahoo.com

Contact Table

ContactID	LastName	LastName	Email
2000	Amit	Kumar	kumarA@get.com
1234	Neil	Am	neil123@microsoft.com
8900	Vikash	Singh	vikashsingh11@info.com

I want to create a report with following fields.

Campaign ID

Campaign Name

Campaign Member ID

Type

Lead/ContactID

First Name

LastName

So FirstName, LastName values should come from Lead table when Type is Lead and if Type is Contact, Firstname and LastName values will looked up from Contact Table.

How this can be implemented?

[PattemManohar · Accepted solution]

@minaxi Please try below steps:

Step 1 : Add a calculated column in CampaignMember Table as below

LeadContactID = IF(Test70CampaignMember[Lead ID]=BLANK(),Test70CampaignMember[ContactID],Test70CampaignMember[Lead ID] )

It will be now as below:

Step 2 : Merge both Lead and Contact table using either Power Query or DAX. In this case, I've used DAX to create a new table as below

Test70LeadContact = UNION(Test70Lead,Test70Contact) 

Note - I've renamed the first field as ID (to be generic)

 

Step 3: Please check and ensure now the relationship looks as below:

That's it !! Now you simple drag the corresponding fields as required and the expected output will be as below

[v-yulgu-msft · Accepted solution]

Hi @minaxi,

Establish a relationship between 'Campaign' and 'Campaign Member'. Create below measures:

Lead/ContactID =
IF (
    SELECTEDVALUE ( 'Campaign Member'[Type] ) = "Lead",
    SELECTEDVALUE ( 'Campaign Member'[Lead ID] ),
    SELECTEDVALUE ( 'Campaign Member'[ContactID] )
)

First Name =
IF (
    SELECTEDVALUE ( 'Campaign Member'[Type] ) = "Lead",
    CALCULATE (
        SELECTEDVALUE ( 'Lead'[First Name] ),
        FILTER ( 'Lead', 'Lead'[LeadID] = [Lead/ContactID] )
    ),
    CALCULATE (
        SELECTEDVALUE ( 'Contact'[FirstName] ),
        FILTER ( 'Contact', 'Contact'[ContactID] = [Lead/ContactID] )
    )
)

Last Name =
IF (
    SELECTEDVALUE ( 'Campaign Member'[Type] ) = "Lead",
    CALCULATE (
        SELECTEDVALUE ( 'Lead'[LastName] ),
        FILTER ( 'Lead', 'Lead'[LeadID] = [Lead/ContactID] )
    ),
    CALCULATE (
        SELECTEDVALUE ( 'Contact'[LastName] ),
        FILTER ( 'Contact', 'Contact'[ContactID] = [Lead/ContactID] )
    )
)

Add corresponding fields and above measures into a Table visual.

Best regards,

Yuliana Gu

[PattemManohar · Accepted solution]

@minaxi Please try below steps:

Step 1 : Add a calculated column in CampaignMember Table as below

LeadContactID = IF(Test70CampaignMember[Lead ID]=BLANK(),Test70CampaignMember[ContactID],Test70CampaignMember[Lead ID] )

It will be now as below:

Step 2 : Merge both Lead and Contact table using either Power Query or DAX. In this case, I've used DAX to create a new table as below

Test70LeadContact = UNION(Test70Lead,Test70Contact) 

Note - I've renamed the first field as ID (to be generic)

 

Step 3: Please check and ensure now the relationship looks as below:

That's it !! Now you simple drag the corresponding fields as required and the expected output will be as below

[minaxi]

@PattemManohar

It worked for me. Thanks for your quick reply. 🙂