topic Re: Percentage of Participation of User From First Date Participated in DAX Commands and Tips

Percentage of Participation of User From First Date Participated

Sherod — Fri, 20 Aug 2021 14:56:56 GMT

Hi all,

We have an application/database that is keeping up with event dates and the participation of users for each of those dates. I've been trying to use DAX to build a measure that would calculate the percentage of participation for a user since their first event. In the example below, there are two people represented by a PersonId, there is an event represented by a PlanId, and a Date column for each event. There are two PlanId's where both people participate, PlanId's 55 and 60.

What I'd specifically like to have is a measure that finds the first date of participation for PersonId = 2 (which is PlanId 52 on Sunday, June 27, 2021) and then calculate the percentage of PlanId's that PersonId = 2 attended out of the possible events that he/she could have attended from the first date they attended (which is on PlanId52)

Total Events in Table --> 28
First Event for Person 2 --> PlanId = 52
Total Events from First Event of Person 2 --> 23
Number of Events Attended of Person 2 --> 13
Person 2 Percentage Attended from First Event Attended --> 13 / 23 = .5652 (56.52%)

Any help regarding the DAX needed to calculate the Percentage a Person has Participated from their first event would be greatly appreciated!

Example Table

PersonId	PlanId	Date
1	48	Wednesday, June 16, 2021
1	49	Sunday, June 20, 2021
1	50	Tuesday, June 22, 2021
1	51	Wednesday, June 23, 2021
2	52	Sunday, June 27, 2021
1	53	Tuesday, June 29, 2021
2	54	Wednesday, June 30, 2021
1	55	Sunday, July 4, 2021
2	55	Sunday, July 4, 2021
2	56	Tuesday, July 6, 2021
1	57	Thursday, July 8, 2021
1	58	Sunday, July 11, 2021
2	59	Tuesday, July 13, 2021
1	60	Wednesday, July 14, 2021
2	60	Wednesday, July 14, 2021
1	61	Sunday, July 18, 2021
1	62	Tuesday, July 20, 2021
2	63	Wednesday, July 21, 2021
2	64	Sunday, July 25, 2021
2	65	Tuesday, July 27, 2021
2	66	Wednesday, July 28, 2021
1	67	Sunday, August 1, 2021
1	68	Wednesday, August 4, 2021
2	69	Sunday, August 8, 2021
1	70	Tuesday, August 10, 2021
2	71	Wednesday, August 11, 2021
2	72	Sunday, August 15, 2021

Re: Percentage of Participation of User From First Date Participated

danielwelch — Fri, 20 Aug 2021 15:29:45 GMT

Hi Sherod does this help:
(Calculated Column)

Calc =

Var FirstID =

CALCULATE(min(Example[PlanId]),filter(All(Example),

Example[PersonId]=earlier(Example[PersonId])))

Return

Var FirstdateSelc =

Calculate(values(Example[Date]),filter(all(Example),

Example[PersonId]=EARLIER(Example[PersonId]) &&

Example[PlanId]=FirstID))

Return

Var Countofeventspossible =

CALCULATE(count(Example[Date]),filter(all(Example),

Example[Date]>=FirstdateSelc))

Return

VAR Countofeventsattended =

Calculate(count(Example[Date]),FILTER(ALL(Example),

Example[PersonId]=EARLIER(Example[PersonId]) &&

Example[Date]>=FirstdateSelc))

Return

DIVIDE(Countofeventsattended,Countofeventspossible)

You can also amend to just use the Variables if you want their information only.

Let me know if this helped.

Daniel.

Re: Percentage of Participation of User From First Date Participated

Sherod — Fri, 20 Aug 2021 17:24:53 GMT

Hi Daniel,

It seems close but the

earlier(Example[PersonId])

function has a message of "Parameter is not the correct type"

I forgot to include the name of the table, which is "Schedules". I tried including the correct table name, of course, but it returned that message above.

I don't know if it matters that PersonId is formatted as a whole number or not...

Any ideas?

Thank you so much for your help,

Sherod

Re: Percentage of Participation of User From First Date Participated

danielwelch — Fri, 20 Aug 2021 17:52:14 GMT

Hi,

mare you able to paste your code and I can see what the error message might be relating to? The id columns should be formatted as whole number so that's fine.

Re: Percentage of Participation of User From First Date Participated

Sherod — Fri, 20 Aug 2021 17:58:01 GMT

Below is a copy of the code and also a screenshot of the error in context of the screen.

Calc = Var FirstID = CALCULATE(min(Schedules[PlanId]),filter(All(Schedules), Schedules[PersonId]=earlier(Schedules[PersonId]))) Return Var FirstdateSelc = Calculate(values(Schedules[Date]),filter(all(Schedules), Schedules[PersonId]=EARLIER(Schedules[PersonId]) && Schedules[PlanId]=FirstID)) Return Var Countofeventspossible = CALCULATE(count(Schedules[Date]),filter(all(Schedules), Schedules[Date]>=FirstdateSelc)) Return VAR Countofeventsattended = Calculate(count(Schedules[Date]),FILTER(ALL(Schedules), Schedules[PersonId]=EARLIER(Schedules[PersonId]) && Schedules[Date]>=FirstdateSelc)) Return DIVIDE(Countofeventsattended,Countofeventspossible)

Screenshot

Re: Percentage of Participation of User From First Date Participated

danielwelch — Fri, 20 Aug 2021 18:17:23 GMT

So it will work if you run the dax in a calculated column in the schedules table as opposed to a measure. The calucations will not nativley move to a measure as they have a number of row based calculations.

Easiest way I would suggest you can visualise this is aggregating the calc column by average and then any table filters you apply in your dashboard will be representative:

Let me know if this is not clear.

Daniel.

Re: Percentage of Participation of User From First Date Participated

Sherod — Fri, 20 Aug 2021 18:30:23 GMT

Oh okay! Yeah I see. I was trying to drop the code you provided into a new measure, but I instead needed to drop it in as a new column. Make sense now. Thank you so much! I tried to get this for days. You were a big help.