Count staff that move between roles

[jmilne320]

Hi All,

I am new to Power BI and I am struggling to create a measure that counts staff of different categories on a particular date.

My model is as below:

The 'Events' table records include a staff number, post ID, and a date on which that staff member attained that post ID. Each post ID corresponds to a fleet, rank, and status in the 'Post' table.

Staff Number	Post ID	Date
123456	2	09/05/2022
234567	15	14/05/2022

I am trying to count the current number of pilots for each fleet, rank & status, but I cannot work out how to count only the most recent post ID for each pilot. I know that a row-specific context is required to check each staff number, but I am unsure how to confirm a record includes the most recent date in the table for that specific staff number (ie their current post).

Here is my DAX code so far:

Active Staff =
CALCULATE (
    COUNTROWS ( Events ),
    FILTER (
        ALLSELECTED ( Events ),
        Events[Staff Number] = Events[Staff Number]
            && Events[Date] = MAX ( Events[Date] )
    )
)

 And here is the output with my actual dataset.

There are 238 rows in my 'Events' table whilst there are 202 discrete staff numbers, so I feel like I'm getting close, I just need help to fix the context of the date & staff number comparison.

Any advice or guidance would be greatly appreciated!

Kind regards,

James

[tamerj1 · Accepted solution]

Hi @jmilne320 
I hope this is what you're looking for

Active Staff = 
CALCULATE ( 
    COUNTROWS (
        FILTER (
            CALCULATETABLE (
                Pilots,
                CROSSFILTER ( Pilots[Staff Number], events[Staff Number], BOTH )
            ),
            MAXX ( RELATEDTABLE ( Events ), Events[Date] ) <= MAX ( 'Date'[Date] )
        )
    ),
    'Date'[date] <= MAX ('Date'[date] )
)

[tamerj1 · Accepted solution]

Hi @jmilne320 
I hope this is what you're looking for

Active Staff = 
CALCULATE ( 
    COUNTROWS (
        FILTER (
            CALCULATETABLE (
                Pilots,
                CROSSFILTER ( Pilots[Staff Number], events[Staff Number], BOTH )
            ),
            MAXX ( RELATEDTABLE ( Events ), Events[Date] ) <= MAX ( 'Date'[Date] )
        )
    ),
    'Date'[date] <= MAX ('Date'[date] )
)

[jmilne320]

That's incredible tamerj1, thank you so much! I never would have been able to come to that solution by myself.

I've marked this as the solution to my inital question, but seeing as you've been so helpful I do have one further question. Is there a way to decrease the count of a posting as a staff member moves to another posting? Ie in the example above in the period 22R07 there is 1 A320 Captain CTL and 1 A320 Captain Office who should then decrease to 0 in 22R08 as they both become F100 Captain Office.

Kind reagrds,

James

[tamerj1 · Accepted solution]

Hi @jmilne320 
First you need to create a new column for end date in the vents table:

end date = 
MINX ( 
    FILTER ( 
        CALCULATETABLE ( Events, ALLEXCEPT ( Events, Events[Staff Number] ) ), 
        Events[start date] > EARLIER ( Events[start date] ) 
    ), 
    Events[start date] 
)

Then you need to deactivate or delete the relationship with the date table

The following would be the measure

Active Staff = 
VAR CurrentDate = MAX ( 'Date'[date] )
RETURN
    COUNTROWS (
        FILTER (
            Events,
            VAR StartDate = Events[start date] 
            VAR EndtDate = COALESCE ( Events[End date], DATE ( 3000, 1, 1 ) )
            RETURN
                StartDate <= CurrentDate
                    && EndtDate >= CurrentDate
        )
    )

[jmilne320]

Ahhhhh I can't begin to explain how excited I am! This is exactly what I have been trying to achieve for weeks, I'm very thankful for your help. It is very interesting to see how you went about troubleshooting my problem and the formulas you employed along the way. I'm looking forward to further exploring Power BI!

Kind regards,

James

[tamerj1]

Great! I'm gladto hear that!

Would you please consider marking the latest reply as acceptable solution?

[tamerj1]

Hi @jmilne320 

Please try

Active Staff =
COUNTROWS (
    FILTER (
        CALCULATETABLE (
            Pilots,
            CROSSFILTER ( Pilots[Staff Number], events[Staff Number], BOTH )
        ),
        MAXX ( RELATEDTABLE ( Events ), Events[Date] ) = MAX ( 'Date'[Date] )
    )
)

[jmilne320]

Hi tamerj1, thanks for very much for your reply!

This formula is definitely along the lines of what I am looking for, although it returns no value until I change the MAX('Date'[Date]) to MAX('Events'[Date]).

I have uploaded a test .pbix file to make it easier for you to inspect:

https://easyupload.io/xvrpgo 

My next problem is that the formula does not return a cumulative count of staff, only a count of staff on the date period when they changed posting.

Thank you so much for your assistance, if you need any further information just let me know.

Kind regards,

James