Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
28 | |
21 | |
11 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |