Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Hi Antriksh
using your solution, how can filter the result by "Sub-job number" = 1 ?
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |