Forum Discussion

steadydriver's avatar
steadydriver
Frequent Visitor
3 years ago
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 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_IDStudent_Start_DateStatus_Start_Date
A11/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

B12/10/223/3/23
C12/10/225/5/23

 

 

Is this Possible?

Thanks

Need Help
  • MFelix's avatar
    MFelix
    3 years ago

    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.

5 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      steadydriver
      Frequent Visitor
      3 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        3 years ago

        Hi steadydriver ,

         

        In my test I get the correct result:

         

        Can you share a sample file please?

         

         

         

    • steadydriver's avatar
      steadydriver
      Frequent Visitor
      3 years ago

        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's avatar
        MFelix
        Icon for Super User rankSuper User
        3 years ago

        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.