Need Help with Dax - How can i extract the last cert value based on the latest Application Number

[Sean-OReilly]

Hi Experts

In my example below - I need to calculate a measure that will give me the value of the last "Certiciate Value" based on the highest (last) "application number" for each "Job Number".

I think i need to use a filter and maxx combined but i can't get it to work. Really appreciate some help.

[AntrikshSharma · Accepted solution]

you could try this for caluclated column:

Column = 
VAR JobNum = Sean[Job Number]
VAR ApplicationNum = Sean[Application Number]
VAR MaxApplication =
    CALCULATE (
        MAX ( Sean[Application Number] ),
        ALLEXCEPT ( Sean, Sean[Job Number] )
    )
VAR Result =
    CALCULATE (
        MAX ( Sean[Certificate Value] ),
        FILTER (
            Sean,
            Sean[Job Number] = JobNum
                && Sean[Application Number] = MaxApplication
        ),
        ALL( Sean )
    )
RETURN
    Result

For measure

Measure 6 = 
VAR JobNum =
    SELECTEDVALUE( Sean[Job Number] )
VAR MaxValue =
    CALCULATE ( MAX ( Sean[Application Number] ), ALL ( Sean[Application Number] ) )
VAR Result =
    SUMX (
        FILTER (
            Sean,
            Sean[Job Number] = JobNum
                && Sean[Application Number] = MaxValue
        ),
        Sean[Certificate Value]
    )
RETURN
    Result

[Sean-OReilly]

Hi Antriksh

using your solution, how can filter the result by "Sub-job number" = 1 ?

[AntrikshSharma]

Like the above?

Column = 
VAR JobNum = Sean[Job Number]
VAR ApplicationNum = Sean[Application Number]
VAR MaxApplication =
    CALCULATE (
        MAX ( Sean[Application Number] ),
        ALLEXCEPT ( Sean, Sean[Job Number] ),
        FILTER ( ALL ( Sean[SubJobNumber] ), Sean[SubJobNumber] = 2 ) 
        /*===============================================================================================
        FILTER ( ALL ( Sean[SubJobNumber] ), Sean[SubJobNumber] = 1 ) Can also be written as 
        Sean[SubJobNumber] = 1 as internally "Sean[SubJobNumber] = 1" expands to FILTER & ALL constructs
        =================================================================================================*/
    )
VAR Result =
    CALCULATE (
        MAX ( Sean[Certificate Value] ),
        FILTER (
            Sean,
            Sean[Job Number] = JobNum
                && Sean[Application Number] = MaxApplication
        ),
        ALL ( Sean )
    )
RETURN
    Result

[Sean-OReilly]

Thanks again Antriksh, what does the DAX for the measure look like?

[AntrikshSharma]

I added SUMX in the last so that in case if there are duplicates in the data they can be summed. see the result in second image, just created a duplicate row.

Measure 8 = 
VAR JobNum =
    SELECTEDVALUE ( Sean[Job Number] )
VAR MaxApplication =
    CALCULATE ( MAX ( Sean[Application Number] ), ALL ( Sean[Application Number] ), Sean[SubJobNumber] = 1 )
VAR ListOfJobNumber =
    FILTER (
        ALL ( Sean ),
        Sean[Application Number] = MaxApplication
            && Sean[Job Number] = JobNum
    )
VAR Result =
    SUMX (
        SUMMARIZE ( Sean, Sean[Job Number], Sean[Application Number] ),
        CALCULATE ( SUM ( Sean[Certificate Value] ), KEEPFILTERS ( ListOfJobNumber ) )
    )
RETURN
   Result

[Sean-OReilly]

Hi Antriksh

Sorry for the delay in  coming back to you but only got a chance to try your solution this morning.

Unfortunately it is not working for me. What i type is in screen shot below.

It should be working becuase when i change the highlighted sub-job number below to 0 or 3 (for which there are none in the table) i get Zero results - which is correct. 

The problem is that when i use Sub-job i am still getting duplication in the measure.

[Sean-OReilly]

Thanks a million AntrikshSharma  👍

This solution was spot on 

[AntrikshSharma]

Great!! I am glad I could help! 🙂