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
Sherod
Frequent Visitor

Percentage of Participation of User From First Date Participated

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

PersonIdPlanIdDate
148Wednesday, June 16, 2021
149Sunday, June 20, 2021
150Tuesday, June 22, 2021
151Wednesday, June 23, 2021
252Sunday, June 27, 2021
153Tuesday, June 29, 2021
254Wednesday, June 30, 2021
155Sunday, July 4, 2021
255Sunday, July 4, 2021
256Tuesday, July 6, 2021
157Thursday, July 8, 2021
158Sunday, July 11, 2021
259Tuesday, July 13, 2021
160Wednesday, July 14, 2021
260Wednesday, July 14, 2021
161Sunday, July 18, 2021
162Tuesday, July 20, 2021
263Wednesday, July 21, 2021
264Sunday, July 25, 2021
265Tuesday, July 27, 2021
266Wednesday, July 28, 2021
167Sunday, August 1, 2021
168Wednesday, August 4, 2021
269Sunday, August 8, 2021
170Tuesday, August 10, 2021
271Wednesday, August 11, 2021
272Sunday, August 15, 2021
1 ACCEPTED SOLUTION
danielwelch
Resolver II
Resolver II

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)

danielwelch_0-1629473329525.png

 

danielwelch_1-1629473374277.png

 


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


Let me know if this helped.

Daniel.



View solution in original post

6 REPLIES 6
danielwelch
Resolver II
Resolver II

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)

danielwelch_0-1629473329525.png

 

danielwelch_1-1629473374277.png

 


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


Let me know if this helped.

Daniel.



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

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.

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

Sherod_0-1629482269389.png

 

 



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:

danielwelch_0-1629483221223.png

 

 

 

danielwelch_3-1629483357004.png

 

Let me know if this is not clear.

Daniel.

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.

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.

Top Solution Authors