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.
The raw data is a combination of 3 dimensions (User, Calendar which are dates with a start of week computed, and ExamType) and 1 fact table with the scoring information.
RawData: | ||||
User | Date | WeekOf | Exam | Score |
Tim | 1/2/2019 | 12/31/2018 | Baseline | 75 |
Ellie | 1/3/2019 | 12/31/2018 | Baseline | 90 |
Jon | 1/2/2019 | 12/31/2018 | Baseline | 88 |
Ellie | 1/10/2019 | 1/7/2019 | Update | 91 |
Tim | 1/10/2019 | 1/7/2019 | Update | 90 |
Ellie | 1/21/2019 | 1/21/2019 | Update | 95 |
Sophia | 1/21/2019 | 1/21/2019 | Baseline | 63 |
Sophia | 1/29/2019 | 1/28/2019 | Update | 77 |
Below is what I'm hoping for in a result set from the DAX formula, the AverageScore can be a measure created later... I'm looking for Scores and Users by week. Each User will take a "Baseline" ExamType and get a score, after they get their intial baseline score, they can update it by taking an "Update" ExamType and changing their score. I care the latest ExamType for each user within the Week context.
Intended DAX Result Set: | |||
Week Of | Scores (numerator) | Users (denominator) | AverageScore |
12/31/2018 | 253 | 3 | 84.33333333 |
1/7/2019 | 269 | 3 | 89.66666667 |
1/14/2019 | 269 | 3 | 89.66666667 |
1/21/2019 | 336 | 4 | 84 |
1/28/2019 | 350 | 4 | 87.5 |
Description of what the result set is showing us:
Week Of | Description of Activity |
12/31/2018 | Tim, Ellie, Jon take baseline exam (we sum their scores and divide by the 3 users to get average) |
1/7/2019 | Tim and Ellie take update exam and change score from baseline (we sum Tim/Ellies Update and Jons baseline, divide by 3) |
1/14/2019 | no activity, same as prior week |
1/21/2019 | Ellie takes update exam and changes scores, Sophia takes baseline exam, User count increases (we take Ellies latest Update exam score, Tims Update score, Jon and Sophias baseline exam scores because they don't have an update available) |
1/28/2019 | Sophia takes update exam and changes score, user count stays |
Any help is greatly appreciated, this has been a brain buster for me for awhile!
@AnonymousI think that I have what you may need.
I was succesful in feeding your logic to come up with a measure that makes the appropriate numbers of rows (all rows with WeekOf <= curent filter context WeekOf date) visible to the current filter context (WeekOf) for the calculation
Numerator := VAR _1 = MAX ( 'Table'[WeekOf] ) VAR _3 = SUMMARIZE ( FILTER ( ALL ( 'Table' ), 'Table'[WeekOf] <= _1 ), [User], "X", MAX ( [WeekOf] ), "Y", MAX ( [Score] ) ) VAR _5 = SUMX ( _3, [Y] ) RETURN _5 Denominator := VAR _1 = MAX ( 'Table'[WeekOf] ) VAR _3 = SUMMARIZE ( FILTER ( ALL ( 'Table' ), 'Table'[WeekOf] <= _1 ), [User], "X", MAX ( [WeekOf] ), "Y", MAX ( [Score] ) ) VAR _5 = COUNTX( _3, [Y] ) RETURN _5
Hi @Anonymous
Please find the PBIX.
Based on your source data I have created the Intended DAX results.
What I did was to create the 3 measures as detailed in the PBIX.
I then used the column called WeekOf to get it showing the values per week.
If there are any additional questions please let me know.
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |