cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sean-OReilly
Helper III
Helper III

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

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.

 

Sean-OReilly_0-1594372555070.png

 

 

 

1 ACCEPTED SOLUTION
AntrikshSharma
Resident Rockstar
Resident Rockstar

you could try this for caluclated column:

s2.PNG

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

s3.PNG

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

 

View solution in original post

8 REPLIES 8
AntrikshSharma
Resident Rockstar
Resident Rockstar

you could try this for caluclated column:

s2.PNG

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

s3.PNG

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

 

View solution in original post

Hi Antriksh

 

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

 

Sean-OReilly_0-1594545620410.png

 

1.PNG2.PNG

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

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

 

12.PNG13.PNG

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

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_0-1595070758632.png

 

Thanks a million AntrikshSharma  👍

This solution was spot on 

Great!! I am glad I could help! 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.