Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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!
Solved! Go to Solution.
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 ) )
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 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |