Forum Discussion
How to shape this data
I need to create a chart that shows by month, how many first time registrants there are vs how many repeat registrants, relative to that point in time. I am stuck on how to manipulate the data to accomplish this.
Here's the current data I have. I'm stumped on how to manipulate this.
| Contact ID | Campaign ID | Campaign Date |
| Joe | Webinar A | January 2020 |
| Joe | Webinar B | June 2020 |
| Sally | Webinar B | June 2020 |
| Joe | Webinar C | November 2020 |
| Sally | Webinar C | November 2020 |
Example: Joe registers for webinar A in January, webinar B in June, webinar C in November. In January, he was a first-time registrant. In June, he is a repeat registrant.
Based on this data table, I want a chart that shows:
- January (webinar A): 1 first-timer (Joe), 0 repeats
- June (webinar B): 1 first-timer (Sally), 1 repeat (Joe)
- November (webinar C): 0 first-timers, 2 repeats (Joe and Sally)
Anonymous .
You can use this code to create a new calculated column:
FirstDate = IF('Table'[Campaign Date] = CALCULATE(MIN('Table'[Campaign Date]), ALLEXCEPT('Table', 'Table'[Contact ID])), "First-Time", "Repeat")
3 Replies
Anonymous .
You can use this code to create a new calculated column:
FirstDate = IF('Table'[Campaign Date] = CALCULATE(MIN('Table'[Campaign Date]), ALLEXCEPT('Table', 'Table'[Contact ID])), "First-Time", "Repeat")Here are two measure expressions to use in a table visual with the Campaign Date column.
Repeat =
VAR maxdate =
MAX ( Events[Campaign Date] )
RETURN
COUNTROWS (
FILTER (
DISTINCT ( Events[Contact ID] ),
NOT (
ISBLANK (
CALCULATE (
COUNT ( Events[Campaign ID] ),
ALL ( Events[Campaign Date] ),
Events[Campaign Date] < maxdate
)
)
)
)
) + 0
First Timers =
VAR maxdate =
MAX ( Events[Campaign Date] )
RETURN
COUNTROWS (
FILTER (
DISTINCT ( Events[Contact ID] ),
ISBLANK (
CALCULATE (
COUNT ( Events[Campaign ID] ),
ALL ( Events[Campaign Date] ),
Events[Campaign Date] < maxdate
)
)
)
) + 0Regards,
Pat
