Dax to Build a Dynamic Table which Changes on Slicer - Which Groups By and Max Value Based on Slicer

[steadydriver]

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 the slicer dates

-Only return 1 Record Per Student, where the Status_Start_Date is the max which is equal to for less than the slicer end date.

For Example, with the below records

If the Slicer was Set

Min = 1/9/22

Max 8/5/23

A Temp Table would show

student_ID	Student_Start_Date	Status_Start_Date
A1	1/9/22	5/5/23 *Note it would not show the record 9/11/22 as the slicer max is not yet at this mate
B1	2/10/22	3/3/23
C1	2/10/22	5/5/23

Is this Possible?

Thanks

[MFelix]

Hi @steadydriver ,

You need to create a calendar table, then add the following measure:

User Within dates =
IF (
    SELECTEDVALUE ( StudentsStatus[status_star_date] )
        = MAXX (
            SUMMARIZE (
                FILTER (
                    ALLSELECTED ( StudentsStatus ),
                    StudentsStatus[Student_ID]
                        IN VALUES ( StudentsStatus[Student_ID] )
                            && StudentsStatus[status_star_date] <= MAX ( 'Calendar'[Date] )
                ),
                StudentsStatus[Student_ID],
                "date", MAX ( StudentsStatus[status_star_date] )
            ),
            [DATE]
        ),
    1
)

Now you can use this as a filter on your table visualization:

In this case I'm using it in the visual itself but it does not need to be there:

[steadydriver]

  Hi for some reason I don't seem to have the option to upload by pbix, are you able to upload your example?  Thanks

[MFelix · Accepted solution]

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.

[steadydriver]

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]

Hi @steadydriver ,

In my test I get the correct result:

Can you share a sample file please?