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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
patri0t82
Post Patron
Post Patron

Measure for MAX Date, for Every Record

Hello, I have two columns.

 

Schedule[Date] and Schedule[Job],

I'm trying to create a measure that finds the MAX [Date] for every record in [Job]. Some of the formulas I've tried (and no longer remember) seemed to bring back some records, but I think it was only the maximum record per date, whereas I need the maximum record per date, per job.

 

2 ACCEPTED SOLUTIONS

@patri0t82 

My apologies. I misunderstood what you were after (I though you only wanted one name based on the last modified date). See if this measure returns what you expect (even in Direct Query?)

Latest Job by date =
VAR _MaxDateTime =
    CALCULATE (
        MAX ( 'Main Table'[ModifiedDate] ),
        FILTER (
            ALLSELECTED ( 'Main Table' ),
            'Main Table'[Name] = MAX ( 'Main Table'[Name] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Main Table'[Job] ),
        FILTER ( 'Main Table', 'Main Table'[ModifiedDate] = _MaxDateTime )
    )

Sample Gif.gif

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

@patri0t82 

Thinkg about it, I don't think my last suggestion delivers what you need, since it shows the last job by name, but ignores that las modified date. 

So see if this measure returns the job by last modified date:

Latest job by modified date =
VAR _MaxDateTime =
    CALCULATE (
        MAX ( 'Main Table'[ModifiedDate] ),
        FILTER (
            ALLSELECTED ( 'Main Table' ),
            'Main Table'[Job] = MAX ( 'Main Table'[job] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Main Table'[Job] ),
        FILTER ( 'Main Table', 'Main Table'[ModifiedDate] = _MaxDateTime )
    )

result 2.jpg

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

28 REPLIES 28
patri0t82
Post Patron
Post Patron

Thank you everyone for your help with this issue. Unfortunately I'm not sure a solution is readily available, at least for use with Direct Query.

I've switched to import mode and the following code appears to provide what I'm looking for in Power Query:

let
    Source = MySource,
    #"Grouped Rows" = Table.Group(Source, {"ScheduleDate", "Shift", "Area", "Job", "JobAbbr"}, {{"AllRows", each _, type table [ScheduleDate=nullable datetime, Name=text, Shift=nullable text, Area=nullable text, Job=nullable text, CalculatedDate=nullable number, DateTimeRecordAdded=datetime, CreatedByUser=nullable text, JobAbbr=nullable text, colScheduleDate=nullable datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([AllRows],"DateTimeRecordAdded")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Name", "DateTimeRecordAdded"}, {"Name", "DateTimeRecordAdded"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AllRows"})
in
    #"Removed Columns"

@patri0t82 

My apologies. I misunderstood what you were after (I though you only wanted one name based on the last modified date). See if this measure returns what you expect (even in Direct Query?)

Latest Job by date =
VAR _MaxDateTime =
    CALCULATE (
        MAX ( 'Main Table'[ModifiedDate] ),
        FILTER (
            ALLSELECTED ( 'Main Table' ),
            'Main Table'[Name] = MAX ( 'Main Table'[Name] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Main Table'[Job] ),
        FILTER ( 'Main Table', 'Main Table'[ModifiedDate] = _MaxDateTime )
    )

Sample Gif.gif

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thank you!

@patri0t82 

Thinkg about it, I don't think my last suggestion delivers what you need, since it shows the last job by name, but ignores that las modified date. 

So see if this measure returns the job by last modified date:

Latest job by modified date =
VAR _MaxDateTime =
    CALCULATE (
        MAX ( 'Main Table'[ModifiedDate] ),
        FILTER (
            ALLSELECTED ( 'Main Table' ),
            'Main Table'[Job] = MAX ( 'Main Table'[job] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Main Table'[Job] ),
        FILTER ( 'Main Table', 'Main Table'[ModifiedDate] = _MaxDateTime )
    )

result 2.jpg

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Ahh, you're right. Great catch, and thank you again!!

v-jianboli-msft
Community Support
Community Support

Hi @patri0t82 ,

 

Based on your description, it seems that you want to first filter for the job corresponding to the most recent modification date and then get the name of the person corresponding to that job?

If so, please try:

 

Measure =
VAR _a =
    CALCULATE (
        MAX ( 'Table1'[ModifiedDate] ),
        FILTER (
            ALL ( Table1 ),
            [Area] = SELECTEDVALUE ( Table1[Area] )
                && [ScheduleDate] = SELECTEDVALUE ( Table1[ScheduleDate] )
        )
    )
VAR _b =
    CALCULATE (
        MAX ( 'Table1'[Job] ),
        FILTER (
            ALL ( Table1 ),
            [ModifiedDate] = _a
                && [Area] = SELECTEDVALUE ( Table1[Area] )
                && [ScheduleDate] = SELECTEDVALUE ( Table1[ScheduleDate] )
        )
    )
VAR _c =
    SELECTCOLUMNS ( 'Table1', "Job", [Job] )
RETURN
    IF ( _b IN _c, _b, BLANK () )

 

Final output:

vjianbolimsft_0-1669098160966.png

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for the response. You are correct, however, in your example, you have selected on November 22 for Area1 and are only showing 3 people, and there should be 9.

v-jianboli-msft
Community Support
Community Support

Hi @patri0t82 ,

 

Based on your description, I have created a simple sample:

vjianbolimsft_2-1668748811144.png

 

vjianbolimsft_1-1668748709541.png

Please try:

Measure = MAX('MyTable'[ModifiedDate])

Final output:

vjianbolimsft_3-1668748823940.png

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for the response. Unfortunately I need to place it in a matrix to show the result from JobAbbr

patri0t82
Post Patron
Post Patron

I think I'm leaving some data out. My apologies.

 

I have CalendarTable[Date] which is connected to MyTable[Date]

 

There's another column called MyTable[ModifiedDate], which is a more specific datetimestamp.

 

If I have Job1 posted in MyTable[Job] more than once on the same Date, I need to pull the one with the most recent [ModifiedDate]

 

 

patri0t82
Post Patron
Post Patron

This formula:

MAX JobAbbr = 
VAR Job = SELECTEDVALUE( MyTable[Job] )

RETURN
MAXX(
    FILTER(  MyTable, MyTable[JobAbbr] = Job ), 
    MyTable[Date] )

 

Appears to almost work, but it's bringing back the MAX Date, and not the Job for that Date

Can you provide a depiction of the expected result?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hello again, the SQL solution apparently wasn't a solution after all.. anyway, I'll attempt to be more clear here. My apologies.

 

The code I'm trying now is:

MAX JobAbbr = 

VAR Job = SELECTEDVALUE( MyTable[Job] )

RETURN
MAXX(
    FILTER(  MyTable, MyTable[Job] = Job ), 
    MyTable[ModifiedDate] )

 

this is what it is returning in my Matrix:

Screenshot 2022-11-18 092219.png

 

This last picture should really help identify what the problem is:

Screenshot 2022-11-18 092840.png

 

As you can see, both people were originally scheduled to work on 11/20/2022, however a newer record was created for Bill, who replaced Adam.

In my matrix on Bill's row, I need to show MF beside Bill's name for 11/20, not Adam's name. And it has to show the Job, not the DateModified.

 

Hopefully that makes sense.. sorry again, I really appreciate the help.

So where is the filter context for the job in the matrix? Do you have a slicer to select the job?

The reason I'm asking is what happens if a name has more than one job on the same date? what job should be displayed for the date for Bill in this example?

which.jpg





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






That's a great question. The report should only display the most recent ModifiedDate on a given ScheduleDate. In your example it should be showing Job2. And thank you again.

See if this works for you.

The model:

model.jpg

The measure

Latest Job by date =
VAR _MaxDateTime =
    CALCULATE (
        MAX ( 'Main Table'[Modified Date] ),
        ALLEXCEPT ( 'Main Table', 'Job Table'[Schedule Job], 'Date Table'[Date] )
    )
RETURN
    CALCULATE (
        MAX ( 'Main Table'[Schedule Job] ),
        FILTER ( 'Main Table', 'Main Table'[Modified Date] = _MaxDateTime )
    )

result.jpg

You can use the same pattern to find the latest name by job:

Latest name by job and date =
VAR _MaxDateTime =
    CALCULATE (
        MAX ( 'Main Table'[Modified Date] ),
        ALLEXCEPT ( 'Main Table', 'Job Table'[Schedule Job], 'Date Table'[Date] )
    )
RETURN
    CALCULATE (
        MAX ( 'Main Table'[Name] ),
        FILTER ( 'Main Table', 'Main Table'[Modified Date] = _MaxDateTime )
    )

name by project.jpgSample file attached

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Standby. Good dummy data coming in a few minutes. Thank you

This is awesome - I'm trying to make it work; considering all my records are within one table, what is the reason for splitting them into different tables?

For instance, if I modify your code to match the correct columns, I feel like it should work but it doesn't:


 

 

 

MAX JobAbbr = 
VAR _MaxDateTime =
    CALCULATE (
        MAX ( MyTable[ModifiedDate] ),
        ALLEXCEPT ( MyTable, MyTable[Job], MyTable[ScheduleDate] )
    )
RETURN
    CALCULATE (
        MAX ( MyTable[Job] ),
        FILTER ( MyTable, MyTable[ModifiedDate] = _MaxDateTime )
    )

 

 

 

 

If I do a distinct Job table, it may not work as there are a couple jobs with the same name that actually fall under multiple other categories.

 

Like:

Area1: Job1

Area1: Job2

Area2: Job1

 

I think I'd have to do Keys and link the Keys together......but I guess I can't do that using DirectQuery

It is considered a best practice to model the data into fact tables and dimension tables. If you have more columns (such as area) these need to be taken into account in the measures. 
It would really  help if you provided a sample of dummy data (actual data sample; not an image)

 

BTW, if you have more than one job for a name on a date (as per my question earlier, we can actually display both jobs on the date if it's relevant)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Here is the data I am working with. Thank you so much for your patience.

https://docs.google.com/spreadsheets/d/1E4Fv2nmsnG9kWrs_7q6MTBKktGnWZ7De/edit?usp=share_link&ouid=11... 

In the dummy data I believe ScheduleDate Nov 22 for Area1 should have 9 jobs filled by 9 people, or close to that anyway.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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