Forum Discussion
Row Context
Here is one way to do it. This makes a virtual table of each resource and two columns - the max date within this role and the max date across all roles. It then counts only those where those snapshot dates are the same.
NewMeasure =
VAR summary =
ADDCOLUMNS (
DISTINCT ( Sheet1[resource_key] ),
"@maxdate",
CALCULATE (
MAX ( Sheet1[snaphot_stamp] )
),
"@maxall",
CALCULATE (
MAX ( Sheet1[snaphot_stamp] ),
ALL ( Sheet1[primary_role] )
)
)
RETURN
COUNTROWS (
FILTER (
summary,
[@maxdate] = [@maxall]
)
)Regards,
Pat
The measure works as designed. You did not include the resource key in any visual so it has no impact on the result.
Please be more specific what you are trying to achieve.
lbendlin Hey there! thanks for the reply... so, the first visual gives me exactly what i require, which is a distinct count of resource_key based on the max snaphot date in that month.
What i am trying to acheive in the other visual is to show the distinct count of resource_key, again based on max snapshot data in the month, but then break that down to show it by primary_role... however, as an example, resource_key = 5048382 on 1/10/10 had a primary_role = _human resource manager, on 12/10/20 this had changed to _P&C Business Partner... the visual is actually picking up both of these, when what i really require is for it to only pick up the primary_role associated with the max snapshot date of 12/10/20. This would then mean that the numbers on visual 1 for the month would equal those on visual 2....
Cheers
Andy
Here is one way to do it. This makes a virtual table of each resource and two columns - the max date within this role and the max date across all roles. It then counts only those where those snapshot dates are the same.
NewMeasure =
VAR summary =
ADDCOLUMNS (
DISTINCT ( Sheet1[resource_key] ),
"@maxdate",
CALCULATE (
MAX ( Sheet1[snaphot_stamp] )
),
"@maxall",
CALCULATE (
MAX ( Sheet1[snaphot_stamp] ),
ALL ( Sheet1[primary_role] )
)
)
RETURN
COUNTROWS (
FILTER (
summary,
[@maxdate] = [@maxall]
)
)Regards,
Pat
mahoneypat That's perfect! thanks Pat, much apprecaited... Andy
