Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a DB with Sessions as this:
Lead_id__c | Session_Start_Time__c | First Lead Conversion Session Time | Session ID | Channel | Traffic Type |
x1 | 11/18/2019 18:32 | 11/26/2019 0:00 | s100 | Non-Direct | |
x1 | 11/19/2019 19:26 | 11/26/2019 0:00 | s101 | Yahoo | Non-Direct |
x1 | 11/25/2019 20:26 | 11/26/2019 0:00 | s102 | Direct | Direct |
x3 | 7/20/2019 6:11 | 1/15/2020 0:00 | s108 | Direct | Direct |
x3 | 11/21/2019 22:50 | 1/15/2020 0:00 | s105 | Non-Direct | |
x3 | 5/15/2020 9:38 | 1/15/2020 0:00 | s106 | Non-Direct | |
x3 | 7/19/2020 18:10 | 1/15/2020 0:00 | s107 | Yahoo | Non-Direct |
x3 | 7/23/2020 6:24 | 1/15/2020 0:00 | s109 | Direct | Direct |
x3 | 7/23/2020 8:10 | 1/15/2020 0:00 | s110 | Direct | Direct |
x3 | 8/2/2020 15:46 | 1/15/2020 0:00 | s111 | Non-Direct | |
x3 | 8/3/2020 7:18 | 1/15/2020 0:00 | s112 | Search | Non-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-
---
This gives me this:
I expected this:
Filter = Non-Direct | |
Row Labels | First Touch |
1 | |
1 | |
Grand Total | 2 |
Filter = Direct | |
Row Labels | First Touch |
Direct | 2 |
Grand Total | 2 |
No Filter | |
Row Labels | First Touch |
Direct | 1 |
1 | |
Grand Total | 2 |
Any suggestions? PBX File : https://www.dropbox.com/t/dNHdObA0HFUjVDwB
Solved! Go to 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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 | 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 | Non-Direct |
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@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".
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
Proud to be a Super User!
Check out my blog: Power BI em Português@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
Proud to be a Super User!
Check out my blog: Power BI em Português@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:
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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |