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