Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jmilne320
Frequent Visitor

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:

jmilne320_0-1653457854773.png

 

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.

 

jmilne320_3-1653458939889.png

 

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

 

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

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

1.png

View solution in original post

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

3.png

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

1.png

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

2.png

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

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

1.png

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

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

3.png

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

1.png

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

2.png

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

Great! I'm gladto hear that!

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

tamerj1
Super User
Super User

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

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.