Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

First Touch Attribution DAX Measure with end-user filter

I have a DB with Sessions as this:

Lead_id__cSession_Start_Time__cFirst Lead Conversion Session TimeSession IDChannelTraffic Type
x111/18/2019 18:3211/26/2019 0:00s100GoogleNon-Direct
x111/19/2019 19:2611/26/2019 0:00s101YahooNon-Direct
x111/25/2019 20:2611/26/2019 0:00s102DirectDirect
x37/20/2019 6:111/15/2020 0:00s108DirectDirect
x311/21/2019 22:501/15/2020 0:00s105LinkedInNon-Direct
x35/15/2020 9:381/15/2020 0:00s106LinkedInNon-Direct
x37/19/2020 18:101/15/2020 0:00s107YahooNon-Direct
x37/23/2020 6:241/15/2020 0:00s109DirectDirect
x37/23/2020 8:101/15/2020 0:00s110DirectDirect
x38/2/2020 15:461/15/2020 0:00s111GoogleNon-Direct
x38/3/2020 7:181/15/2020 0:00s112SearchNon-Direct

 

I would like to have a measure that counts the first session per "Channel" that was before or at the same time as "First Lead Conversion Session Time". I would also like to have a user filter on "traffic type", this filter will impact the 1 st session time as some potential sessions that were first might be filtered out. 

 

This is what I have tried so far:

-First--

I have created a calculated column in the table "session order rank by Lead ID]" that ranks the sessions by dates per lead ID.

-Then-

Lead 1st Touch Attribution Meassure =
var TB =
CALCULATETABLE(
'Lead Attribution',
ALLSELECTED('Lead Attribution'[Lead_id__c])
FILTER(
'Lead Attribution','Lead Attribution'[Session_Start_Time__c]<='Lead Attribution'[First Lead Conversion Session Time]),
TOPN(1,'Lead Attribution','Lead Attribution'[session order rank by Lead ID],ASC)
 
)
 
var calc =
sumx(
TB,
CALCULATE(
DISTINCTCOUNT(
'Lead Attribution'[Session ID])
)
)
 
RETURN calc

 ---

 

This gives me this:

 
 

2020-11-18 15_07_42-Dummy Data - Power BI Desktop.png

I expected this:

Filter = Non-Direct 
Row LabelsFirst Touch
Google1
LinkedIn1
Grand Total2
  
Filter = Direct 
Row LabelsFirst Touch
Direct2
Grand Total2
  
No Filter 
Row LabelsFirst Touch
Direct1
Google1
Grand Total2

 

Any suggestions?  PBX File : https://www.dropbox.com/t/dNHdObA0HFUjVDwB 

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

That has to do with the context replace your measure by:

Counts Lead IDs FirstLead = 
VAR MinimumDatesTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'DIm Channel', 'DIm Channel'[Channel] ),
        "@DateMin", MIN ( 'Lead Attribution'[First Lead Conversion Session Time] )
    )
VAR MinStarDateSelection =
    MINX( MinimumDatesTable, [@DateMin] )

VAR MinStarSessionStartTime =
    CALCULATE(MIN('Lead Attribution'[Session_Start_Time__c]),ALLEXCEPT('Lead Attribution','Lead Attribution'[Lead_id__c],'Lead Attribution'[Traffic Type]))
VAR AttributionFiltered =
    FILTER (
        'Lead Attribution',
        'Lead Attribution'[Session_Start_Time__c] = MinStarSessionStartTime&&'Lead Attribution'[Session_Start_Time__c]<=MinStarDateSelection
    )

VAr CountAXX =COUNTAX(
        AttributionFiltered
        ,DISTINCTCOUNT('Lead Attribution'[Lead_id__c])
    )
Return 
IF(HASONEVALUE('DIm Channel'[Channel]),CountAXX,SUMX(ALLSELECTED('Lead Attribution'[Lead_id__c]),CountAXX))

This may need some changes around the SUMX part.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

7 REPLIES 7
MFelix
Super User
Super User

Hi @Anonymous ,

 

Not really sure if I understand the logic based on your data because I cannot reach the result you present on the end of your post.

 

I have created the following measure:

 

Counts Value FirstLead = 
VAR MinimumDatesTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'DIm Channel'; 'DIm Channel'[Channel] );
        "@DateMin"; MIN ( 'Lead Attribution'[First Lead Conversion Session Time] )
    )
VAR MinStarDateSelection =
    MINX ( MinimumDatesTable; [@DateMin] )
VAR AttributionFiltered =
    FILTER (
        'Lead Attribution';
        'Lead Attribution'[Session_Start_Time__c] <= MinStarDateSelection
    )
RETURN
    COUNTROWS ( AttributionFiltered )

 

Result is on attach file.

 

If this is not the expected result can you share what are the lines that are counting for each of the calculations you present and how you select that is that line.

 

What I assume in my calculation is that you pick up for each channel what is the lowest First Lead and then all the dates that are lower than that.

 

for the No filter I have picked up the following lines:

Lead_id__c Session_Start_Time__c First Lead Conversion Session Time Session ID Channel Traffic Type
x1 18/11/2019 18:32 26/11/2019 00:00 s100 Google Non-Direct
x1 19/11/2019 19:26 26/11/2019 00:00 s101 Yahoo Non-Direct
x1 25/11/2019 20:26 26/11/2019 00:00 s102 Direct Direct
x3 20/07/2019 06:11 15/01/2020 00:00 s108 Direct Direct
x3 21/11/2019 22:50 15/01/2020 00:00 s105 LinkedIn Non-Direct

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix You are on to something. This is how I end up with my expected filtered results. I guess the inclusion of the "First Lead Conversion Session Time" is superfluous in this calculation but is important when I will do the same for the last touch happening before or at "First Lead Conversion Session Time". 2020-11-20 17_55_36-Example Data - Excel.png

Hi @Anonymous ,

 

Sorry but still not getting wath is the dates you want to compare and with what values.

 

Looking at you data what is the minimum date that you compare to and why you consider one date or not in your calculation.

 I'm only confused about why you consider some dates, for example when you have no filter why is the value consider the 18 November and not July 20? Is it because the Sesseion is lower or because the first lead is lower?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix Sorry if I've been unclear. Maybe if I start from the beginning. I need to attribute a campaign to each Lead ID and then in my visual sum the lead IDs to the "First Touch" Campaign that attributed given the filter. Your question about why 18th of November over July 20 is because the lead ID is different. 

Hi @Anonymous ,

 

So the first information that sets the date is the Lead ID number? so the lowest Lead ID is the one that the date is consider?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix No, it's per lead ID I want to find the first session per lead ID. In the case where no filters are applied on the traffic type, this is S100 for lead id x1 and s108 for lead x3. S100 has "Google" as Channel and s108 has "Direct" hence:

karlal_0-1606134985659.png

I have come a little closer with the inspired by your initial reply. 

Counts Lead IDs FirstLead = 
VAR MinimumDatesTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'DIm Channel', 'DIm Channel'[Channel] ),
        "@DateMin", MIN ( 'Lead Attribution'[First Lead Conversion Session Time] )
    )
VAR MinStarDateSelection =
    MINX( MinimumDatesTable, [@DateMin] )

VAR MinStarSessionStartTime =
    CALCULATE(MIN('Lead Attribution'[Session_Start_Time__c]),ALLEXCEPT('Lead Attribution','Lead Attribution'[Lead_id__c],'Lead Attribution'[Traffic Type]))
VAR AttributionFiltered =
    FILTER (
        'Lead Attribution',
        'Lead Attribution'[Session_Start_Time__c] = MinStarSessionStartTime&&'Lead Attribution'[Session_Start_Time__c]<=MinStarDateSelection
    )

VAr CountAXX =COUNTAX(
        AttributionFiltered
        ,DISTINCTCOUNT('Lead Attribution'[Lead_id__c])
    )
Return CountAXX

This gets me to the correct filtering but the Total is incorrect. 

karlal_0-1606216444074.png

 

 

Hi @Anonymous ,

 

That has to do with the context replace your measure by:

Counts Lead IDs FirstLead = 
VAR MinimumDatesTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'DIm Channel', 'DIm Channel'[Channel] ),
        "@DateMin", MIN ( 'Lead Attribution'[First Lead Conversion Session Time] )
    )
VAR MinStarDateSelection =
    MINX( MinimumDatesTable, [@DateMin] )

VAR MinStarSessionStartTime =
    CALCULATE(MIN('Lead Attribution'[Session_Start_Time__c]),ALLEXCEPT('Lead Attribution','Lead Attribution'[Lead_id__c],'Lead Attribution'[Traffic Type]))
VAR AttributionFiltered =
    FILTER (
        'Lead Attribution',
        'Lead Attribution'[Session_Start_Time__c] = MinStarSessionStartTime&&'Lead Attribution'[Session_Start_Time__c]<=MinStarDateSelection
    )

VAr CountAXX =COUNTAX(
        AttributionFiltered
        ,DISTINCTCOUNT('Lead Attribution'[Lead_id__c])
    )
Return 
IF(HASONEVALUE('DIm Channel'[Channel]),CountAXX,SUMX(ALLSELECTED('Lead Attribution'[Lead_id__c]),CountAXX))

This may need some changes around the SUMX part.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.