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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Nazdac2024
Frequent Visitor

Help with Calculating Number of Events in a Nested Service Type Table in Power BI

Hi all,

I’m trying to solve a problem in Power BI with the following dataset structure:

  • The table contains columns: Service Type, Start Date, and End Date.
  • Each service type can have multiple related events (rows).

    Goal:

I need to calculate the number of events for each unique service type entry, based on the following logic:

  1. For each service type row, count all subsequent rows (events) where:
    • The Service Type is blank (nested events).
    • The Start Date is less than or equal to the End Date of the original service type.
  2. If there are identical nested service types within the range, adjust the end date dynamically to include those nested ranges.

Example:

Table Input:

  • Nazdac2024_0-1736455066302.png
 

Steps:

  • For Serv 1 (No 1):
    • The end date is 1/6/2025. Counting all rows where the start date is >= 1/1/2025, I get 5 events.
  • For Serv 2 (No 7):
    • The initial end date is 1/10/2025. However, a nested Serv 2 (Row 9) ends on 1/12/2025, extending the range. Counting events in this range gives 5 events.
  • For Serv 1 (No 26):
    • This is calculated separately, with an end date of 1/22/2025, resulting in 3 events.
      Nazdac2024_1-1736455170104.png

      Does anyone know how I can achieve this?
      I have full control over the data model and can create calculated columns or measures if needed. Any guidance would be appreciated!

      Thanks in advance!

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@Nazdac2024 

For Serv 1 (No 1):

  • The end date is 1/6/2025. Counting all rows where the start date is ≤ 1/6/2025,

should that be start date < 1/6/2025? it's because if <= , then we will get 6 rows

 

11.PNG

 

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ryan_mayu
Super User
Super User

@Nazdac2024 

For Serv 1 (No 1):

  • The end date is 1/6/2025. Counting all rows where the start date is ≤ 1/6/2025,

should that be start date < 1/6/2025? it's because if <= , then we will get 6 rows

 

11.PNG

 

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Great , 
the start date for Serv 1 (No 1) is >= 1/1/2025
sorry for the mistake 

I ll will test this on my data set 🙂 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.