Returning related text value based on min of some other value

[joefusaro]

Hi, I'm trying to build some marketing reports that show the initial type of campaign that each person responded to.

I've extracted some data which is related as follows:

• Person has a one-to-many relationship with CampaignMember
• CampaignMember has a many-to-one relationship with a Campaign.

For each row in the Person table, I would like to get the Campaign[Type] that is related to the CampaignMember with the earliest (lowest) FirstRespondedDate.

So if a Lead has 5 Campaign Members, get the Campaign Member row with the earliest (lowest) FirstRespondedDate, and return the Campaign[Type] that is related to that Campaign Member.

The first step I took was to add a custom column to Campaign Member to get the Type from the related Campaign.

CampaignType = RELATED(Campaign[Type])  

On the Person table, I tried a custom column with this formula:

First Lead Source = LOOKUPVALUE(
	'Campaign Member'[CampaignType],
	'Campaign Member'[FirstRespondedDate],
	MIN('Campaign Member'[FirstRespondedDate])
)

 But I got the error "A table of multiple values was supplied where a single value was expected"

I've tried a few variations on this formula, but I'm stuck. Any help or guidance is greatly appreciated!

[v-chuncz-msft · Accepted solution]

@joefusaro,

You may refer to the following DAX that adds a calculated column.

Column =
CALCULATE (
    SELECTEDVALUE ( 'Campaign Member'[CampaignType] ),
    TOPN (
        1,
        RELATEDTABLE ( 'Campaign Member' ),
        'Campaign Member'[FirstRespondedDate], ASC
    )
)