Forum Discussion

jmilne320's avatar
jmilne320
Frequent Visitor
4 years ago
Solved

Count staff that move between roles

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 NumberPost IDDate
123456209/05/2022
2345671514/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

 

DAX
employee
time
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
    tamerj1
    4 years ago

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

7 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      jmilne320
      Frequent Visitor
      4 years ago

      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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      jmilne320
      Frequent Visitor
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

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