Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
Hi Sherod does this help:
(Calculated Column)
You can also amend to just use the Variables if you want their information only.
Hi Sherod does this help:
(Calculated Column)
You can also amend to just use the Variables if you want their information only.
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
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.
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.
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |