Advanced DAX required: What is the maximum occupancy of the bus during the journey?

[zdenek_moravec]

Dear colleagues,

Imagine, You are a bus company manager and You would like to know, how is Your bus line occupied.

You have a list of all bus tickets in a table. Every record (ticket) consists of ticketID, from_station, to_station and number of passengers.

The question is "What is the maximum occupancy of the bus during the bus journey at any point in time?"

See max bus occupancy.pbix with a simplified fact table (without date) and a dim table with bus stops order. The picture in the report views describes my manual calculation, but I am not able to transform it into a DAX solution.

ChatGPT says "To determine the maxi.mum occupancy of the bus at any point in time, we need to track how many passengers are on board at each stop - calculate occupancy at each stop by summing boardings and subtracting alightings." But the DAX does not work even after many corrections.

Thank You very much for any hint.

Zdenek Moravec

Cesky Krumlov, Czechia

[zdenek_moravec · Accepted solution]

Dear colleagues,

Thank You very much for Your responses. I will go through all solutions and try to apply to my full scenario of 1 mil tickets with many bus lines and dates. I will let You know, which solution is most suitable for me.

Zdenek

[v-saisrao-msft]

Hi @zdenek_moravec,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @zdenek_moravec,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @zdenek_moravec,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @zdenek_moravec,
I wanted to check if you had the opportunity to review the information provided by @ThxAlot @Ashish_Mathur @lbendlin . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[ThxAlot]

Your question is just a variant of common "headcount" question, e.g. count active employees at certain point of periods. In such scenarios, it's not necessary to directly liaison date table (usually dim table) with fact table.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[lbendlin]

Not sure if advanced DAX is required - the source data could be structured better.