cancel
Showing results for
Did you mean:
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.

1 ACCEPTED SOLUTION
Resident Rockstar

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``````

8 REPLIES 8
Resident Rockstar

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``````

Helper III

Hi Antriksh

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

Resident Rockstar

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``````
Helper III

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

Resident Rockstar

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``````
Helper III

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.

Helper III

This solution was spot on

Resident Rockstar
Great!! I am glad I could help! 🙂

Announcements

#### Launching new user group features

Learn how to create your own user groups today!