cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Row Context

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

1 ACCEPTED SOLUTION

Accepted Solutions

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Super User II
Super User II

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors