Forum Discussion
Solved
Dax to Build a Dynamic Table which Changes on Slicer - Which Groups By and Max Value Based on Slicer
Hi I want to build a Dynamic Table in Dax, which changes each time the slicer date changes, it needs to -Return 1 Record Per Student ID -Only Include Students where there start date is between ...
Please see file attach.
File attach is not available for all the users but you can use a onedrive, google drive, we transfer or similar link to upload your files.
Hi
This is doing some of what I want but not all
If I use your measure
User Within dates =
IF (
SELECTEDVALUE ( student_attendance_data[status_start_date] )
= MAXX (
SUMMARIZE (
FILTER (
ALLSELECTED ( student_attendance_data ),
student_attendance_data[Student_ID]
IN VALUES ( student_attendance_data[Student_ID] )
&& student_attendance_data[status_start_date] <= MAX ( 'DataTable'[Date] )
),
student_attendance_data[Student_ID] ,
"date", MAX ( student_attendance_data[status_start_date] )
),
[DATE]
),
1
)
It Solves 1 Problem)
It correctly only includes Student Records where their "Student_Start_Date" is between the Slicer Data
It does not Solve the 2nd Problem
If I put the Slicer End date to (for example) 27/1/23, I want Table A) as below to return the following records with the following Criterial
-This Works - Students with "Student_Start_Date" between Slicer Dates
- This DOES NOT Work - Return the the "Status_Start_Date" which is the Max, but where the MAX is on or before the Slicer End Date.
Therefore if the Slicer Dates where Start 1/9/22 and End 27/1/23 I would want it to Return
Student A1 with the Record where the "Status_Start_date" was 9/11/22 = "Work Placement" as this is the max record with the "Status_Start_Date" on or before the slicer End Date 27/1/23. (And not the record where the Status Start Date is the max unfiltered i.e. 6/7/23).
Student B1 - where "Status_Start_Date" was 2/10/22 - "New", as this was the cloest to the Slicer End Date of 27/1/23.
Student C2 - where "Status_Start_date" was 2/10/22 - "New"
Student D - Would not show as Student D Started on 5/6/23 so the "Student_Start_Date" is now within the slicer Dates.
The issue I have with the data is students can have many records but I need to filter just 1 record and count the Status.
I need to first of all filter
- "Student_Start_Date" is within the Slicer Dates. Note the Date Table is linked to "Student_Start_Date".
- Then I need to further filter the records (where you could have multiple records per student with different "Status_Start_Date", to find the Status Record where the "Status_Start_Date" is the max record which is on or before the slicer end date.
The Link in the model is between "Student_Start_Date" and the Date Table, and NOT "Status_Start_Date", although if I change the relationship to "Status_Start_Date", this doesn't fix the Measure.
I hope this makes sense. Thanks James
MFelix
Super User
Super User