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 IDCampaign NameCampaign Member ID123abc12345123abc1234567345def34557ghk22212kfjd566 Campaign Member Table campaign Member IDTypeLead IDContactID 12345Lead1800 null 1234567Lead1900 null 345Contactnull2000 222Contactnull1234 566Contactnull8900 Lead TableLeadIDFirst NameLastNameEmail1800johnvjohnv@abc.com1900marryJoshephmarryj@yahoo.com Contact TableContactIDLastNameLastNameEmail2000AmitKumarkumarA@get.com1234NeilAmneil123@microsoft.com8900VikashSinghvikashsingh11@info.com I want to create a report with following fields. Campaign IDCampaign NameCampaign Member IDTypeLead/ContactIDFirst NameLastNameSo 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?

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

Frequent Visitor

7 years ago

Solved

Look up values from two different table basis filter conditions

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	[email protected]
1900	marry	Josheph	[email protected]

Contact Table

ContactID	LastName	LastName	Email
2000	Amit	Kumar	[email protected]
1234	Neil	Am	[email protected]
8900	Vikash	Singh	[email protected]

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?

Need Help

PattemManohar
7 years ago
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

7 years ago

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

3 Replies

PattemManohar
Community Champion
7 years ago
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
  Frequent Visitor
  7 years ago
  PattemManohar
  
  It worked for me. Thanks for your quick reply. :)

v-yulgu-msft

Microsoft Employee

7 years ago

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

Forum Discussion

Look up values from two different table basis filter conditions

3 Replies

Related content

Date Different return wrong value

Conditional formatting for difference between bars?

Value conditional formatting

how to handle null values in if condition

Difference Value