Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
If I have a table of records showing each customer contact (phone/video call, in-person meeting), how can I count the number of one type of contact that resulted in another on the same or next day - specifically, how many video calls resulted in an in-person meeting the same or next day?
I've come up with the solution outlined below, but one of the issues I'm encountering with this approach is when I put the measure into a visual and slice by my date table. In instances where the video call occurs at the end of the month, but the meeting occurs at the beginning of the next month (e.g., call on 31 January, meeting on 1 February), the visual does not know which month to put this measure result in. Ideally, the visual would "anchor" with the date of the video call (i.e., the first event - continuing with example above, this would be 31 January). It does work if I use the date from the fact table, but this is not ideal since I can't group by month, etc. How can I address this issue?
Any questions or if more info is needed, please let me know. Thank you for your time and assistance!
My sample data (date table is continuous dates for the full years spanning the data, in this case, just 2020):
Dept | Cust Id | Type | Date |
A | 123 | In-person | 2020-01-01 |
A | 123 | Video | 2020-01-03 |
B | 123 | In-person | 2020-01-05 |
B | 123 | Phone | 2020-01-07 |
C | 234 | Phone | 2020-01-15 |
C | 234 | Video | 2020-01-28 |
A | 234 | Video | 2020-01-29 |
A | 234 | In-person | 2020-01-30 |
A | 345 | In-person | 2020-02-01 |
A | 345 | Phone | 2020-02-02 |
A | 345 | Video | 2020-02-03 |
A | 345 | Phone | 2020-02-03 |
A | 345 | In-person | 2020-02-03 |
B | 234 | Video | 2020-01-31 |
B | 234 | In-person | 2020-02-01 |
The sample model:
The DAX measure I tried:
Count video encounters with in-person visits 0-1 day later =
var VidInpDates =
ADDCOLUMNS (
SUMMARIZE ( 'Customer Encounters',
'Customer Encounters'[Cust Id],
'Customer Encounters'[Type],
'Customer Encounters'[Date]
),
"ThisVidDate", CALCULATE ( MAX ( 'Customer Encounters'[Date] ), 'Customer Encounters'[Type] = "Video" ),
"NextInpDate", CALCULATE (
CALCULATE (
MIN ( 'Customer Encounters'[Date] ),
'Customer Encounters'[Type] = "In-person",
FILTER ( ALL ( 'Customer Encounters'[Date] ),
'Customer Encounters'[Date] >= MAX ( 'Customer Encounters'[Date] )
)
)
)
)
var VidInpDiff =
ADDCOLUMNS (
VidInpDates,
"DaysBtwn", DATEDIFF ( [ThisVidDate], [NextInpDate], DAY )
)
var ZeroToOneCount =
COUNTROWS (
FILTER ( VidInpDiff,
'Customer Encounters'[Type] = "Video"
&& [DaysBtwn] >= 0
&& [DaysBtwn] <= 1
&& NOT ( ISBLANK ( [DaysBtwn] ) )
)
)
return ZeroToOneCount
Sample matrix visuals showing missing measure result when video call occurs in one month, and in-person occurs in another:
Solved! Go to Solution.
Please try this expression, as it gets your expected results from your sample results.
Video In Person 1 Day Count =
VAR summary =
ADDCOLUMNS (
FILTER (
SUMMARIZE (
'Customer Encounters',
'Customer Encounters'[Cust Id],
'Customer Encounters'[Date],
'Customer Encounters'[Type]
),
'Customer Encounters'[Type] = "Video"
),
"@InPerson1Day", CALCULATE (
COUNTROWS ( 'Customer Encounters' ),
ALL ( 'Date' ),
ALL ( 'Customer Encounters'[Date], 'Customer Encounters'[Type] ),
'Customer Encounters'[Type] = "In-person",
FILTER (
ALL ( 'Customer Encounters'[Date] ),
OR (
'Customer Encounters'[Date] = EARLIER ( 'Customer Encounters'[Date] ),
'Customer Encounters'[Date]
= EARLIER ( 'Customer Encounters'[Date] ) + 1
)
)
)
)
RETURN
COUNTROWS(Filter(summary, [@InPerson1Day]>0))
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this expression, as it gets your expected results from your sample results.
Video In Person 1 Day Count =
VAR summary =
ADDCOLUMNS (
FILTER (
SUMMARIZE (
'Customer Encounters',
'Customer Encounters'[Cust Id],
'Customer Encounters'[Date],
'Customer Encounters'[Type]
),
'Customer Encounters'[Type] = "Video"
),
"@InPerson1Day", CALCULATE (
COUNTROWS ( 'Customer Encounters' ),
ALL ( 'Date' ),
ALL ( 'Customer Encounters'[Date], 'Customer Encounters'[Type] ),
'Customer Encounters'[Type] = "In-person",
FILTER (
ALL ( 'Customer Encounters'[Date] ),
OR (
'Customer Encounters'[Date] = EARLIER ( 'Customer Encounters'[Date] ),
'Customer Encounters'[Date]
= EARLIER ( 'Customer Encounters'[Date] ) + 1
)
)
)
)
RETURN
COUNTROWS(Filter(summary, [@InPerson1Day]>0))
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you, @mahoneypat, that solution worked! I'm not very good at using the EARLIER function so this solution provides another example for me to better understand it. Cheers!
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
43 | |
26 | |
21 |