Formula to count occurrences based on dynamic time-frame (slicer)

[ylebec]

Hi,

I'm quite experienced in Excel but new to Power BI and DAX. After some research I could not find anything that would help me with my case.

We have operated 198 bus trips (or bus services) last Friday (Weekday), 163 last Saturday and 141 last Sunday. What we operated usually differs from what it is scheduled, i.e. 209 on Weekdays, 171 on Saturdays and 160 on Sundays. This is due to sometimes not being able to operate everything that's scheduled for the day (Go Public Transport! 🙂 )

I have two tables, one with the scheduled services

and one with the operated ones by date:

Now as you can see the Trip Key metric (a unique identifier for a bus trip) is common to both tables so I have them joined (1 to Many from Scheduled to Operated)

Problem, when I extend the timeframe to include more weekdays as per above table, the scheduled total number remains being 540 (1x Wd + 1x Sat + 1xSun trips), getting a false total for the selected data range.

What I need is a formula to populate in this case 4x209 + 1x171 + 1x160 = 1167 trips scheduled (vs 1071 operated).

Any ideas?

Thanks a lot

[bidevsugmen]

Hi @ylebec

You can consider the below steps to get the expected result.

I have created 2 tables based on the sample data provided by you. Table 1 is the master table and table 2 contains actual number of trips done.

1. In query editor, create custom column (named as index) in table 1.

2. In table 2, create duplicate of service column. 

3. Split the duplicate column based on first occurrence of space.

4. Create custom column (named as index) in this table also.

5.Merge table 1 into table 2 based on Index column and expand it.

6. This is the final output of table 2. 

You can now pull the columns highlighted in the above image into the table visualization. 

And this should give you the expected output as below.

Please do accept it as solution if it works!

 

Thanks,

Suguna.

[ylebec]

Hi Suguna

Thank you for the great tutorial to implement this solution! One thing though... the real master table with the scheduled trips contains over 11K rows! whereas the Operated Services one close to a Million or more, not really sure as these tables are pulled from an Access DB. What I showed on the example was just a minor fraction of the data to simplify.

Now, everything goes according to plan until I have to 'Expand' the table after merging it... due to the insane size it's taking forever.

I had thought of creating a relationship between both tables based on Index and then pull the columns without the expansion... but due to none of them having unique values it is not doable.

Any alternatives?

Y