Measure for MAX Date, for Every Record

[patri0t82]

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.

[PaulDBrown · Accepted solution]

@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 )
    )

[PaulDBrown]

I can't access the file (it's says I need permission).

[patri0t82]

I think it should work now. If not try this

https://docs.google.com/file/d/1E4Fv2nmsnG9kWrs_7q6MTBKktGnWZ7De/edit?usp=docslist_api&filetype=msex... 

I'm certainly not asking or hoping you'll look at this on the weekend though. Can't say enough how thankful I am though. Thank you again 

[PaulDBrown]

Please check to make sure you are getting the results you expect.

The model

I've created the dimension tables in Power Query (except the date table). If you prefer to build them using DAX, use the DISTINCT function as per my other file.

The measure

Latest Job by date =
VAR _MaxDateTime =
    CALCULATE (
        MAX ( 'Main Table'[ModifiedDate] ),
        ALLEXCEPT (
            'Main Table',
            'Area Table'[dArea],
            'Shift Table'[dShift],
            'Job Table'[dJob],
            'Date Table'[Date]
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Main Table'[Job] ),
        FILTER ( 'Main Table', 'Main Table'[ModifiedDate] = _MaxDateTime )
    )

I've also created the following measure to use as a filter for the slicer to show only the relevant options based on slicer selections. (The measure is added to each slicer in the filter pane):

Filter Slicers = 
COUNTROWS(
    RELATEDTABLE('Main Table'))

Sample PBIX file attached

[patri0t82]

I guess the ALLEXCEPT error was occuring because I'm using DirectQuery. Once I switched to import it didn't give me that error.

The second issue still exists though where I'm only seeing one person per day

[patri0t82]

I guess, also, when using your file, if I select 11/22/2022 and Area1,

the table on the right should have several names, with the maximum job, not just Person37, if that makes sense

[patri0t82]

Thank you again, and sorry it's taken this long to respond.

I'm attempting to recreate, using my own tables.

FWIW,

JobCategory = Job

Job = JobAbbr

ModifiedDate = DateTimeRecordAdded

in my file.

I'm getting an ALLEXCEPT error. Can you help me see what's wrong?

[PaulDBrown]

Thanks! I'll probably tackle this tomorrow (it's late in my neck of the woods) if that's ok

Btw, will Area be part of the matrix/a slicer? Or do you want the latest job regardless of the Area?

[patri0t82]

Hi, yes Area, Shift and Date will be slicers.  thanks again for all your help today.