Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
steadydriver
Frequent Visitor

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

 

steadydriver_0-1695120454953.png

 

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

1 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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
MFelix
Super User
Super User

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:

MFelix_0-1695635365390.png

 

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

MFelix_1-1695635403443.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 
 
 
steadydriver_1-1695637468578.png

 

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.

 

steadydriver_2-1695637749171.png

 

 I hope this makes sense.  Thanks James

Hi @steadydriver ,

 

In my test I get the correct result:

MFelix_0-1695639368984.png

MFelix_1-1695639448085.png

 

Can you share a sample file please?

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors