Row Context

[andybamber]

Hello!

Hopefully someone can help me with an issue im facing. In the PBIX below I have a sample of my dataset. If you look at the visuals, the first one correctly shows a distinct count of 8... the second one though, when i try to visualise by role, does not show teh correct count. I presume this is due to row context, and the fact that some resource_keys have a change in role name in the same month. So the question is, how can i build a measure (or any other solution) that will only count/display the most recent occurance for that resource_key

https://www.dropbox.com/s/pvknpdf82o70y72/PBIX_example.pbix?dl=0 

Cheers

Andy

[lbendlin]

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.

[andybamber]

@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

[mahoneypat · Accepted solution]

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

[andybamber]

@mahoneypat  That's perfect! thanks Pat, much apprecaited... Andy