Forum Discussion
Solved
Measure/Model calculation support needed
Dear all, I have a question and I request some support. (I am trying to solve this in Power BI but maybe its a real back-end issue) In Power BI we consume a starschema where different products (...
Hi Hichamas4
You are facing a scenario where products may belong to multiple categories (Bike, Car, Truck), and where each product-category combination has associated date intervals. Your objective is to calculate lead time in days per category while applying three conditions:Categories left blank must be ignored.
Overlapping date intervals must not be counted twice.
Gaps (periods where no interval exists) must not contribute to the total lead time.
This is fundamentally an “interval-union” problem, where multiple date ranges per product and category need to be treated as a single continuous timeline.
1. Recommended Data Modelling Approach
Using separate columns for categories (Group-Bike, Group-Car, Group-Truck) complicates the calculations.
A more suitable modelling approach is to normalise the categories by creating a bridge table.Proposed structure
-
Dim_Product
ProductID, ProductName -
Bridge_ProductCategory
ProductID, Category
(One row per category, for example Bike, Car or Truck)
Fact_ProductTransactions
ProductID, StartDate, EndDateThis structure allows analytical filtering by category without dealing with blank columns, and avoids complex conditional logic.Power Query transformation (conceptual steps)
Duplicate the raw product table.
Unpivot the three category columns.
Remove rows where the category value is blank.
Produce a table containing only ProductID and Category.
This becomes your bridge table linking products to categories.
2. Lead Time Calculation Strategy
The most robust method for handling overlapping intervals and gaps is to use a Date dimension and count the number of distinct days for which at least one interval is active.Because a day is counted only once, overlapping intervals do not accumulate duplicate lead-time.
Days without an interval automatically represent gaps and are excluded.Required tables
Dim_Date
Fact_ProductTransactions
Dim_Product
Bridge_ProductCategory
Dim_Category
Lead Time Measure (DAX):
Leadtime Days :=VAR ActiveDates =FILTER(ALL('Dim_Date'[Date]),CALCULATE(COUNTROWS('Fact_ProductTransactions'),'Dim_Date'[Date] >= 'Fact_ProductTransactions'[StartDate]&& 'Dim_Date'[Date] <= 'Fact_ProductTransactions'[EndDate]) > 0)RETURNCOUNTROWS(ActiveDates)This measure returns the number of days in which at least one interval is active in the current filter context (Product, Category, etc.). Overlaps and gaps are handled implicitly.
3. When to Move Logic to the Back‑End
If the volume of data is high, or if the Date dimension spans many years, the DAX approach may become slow.
In that case, it is advisable to consolidate intervals in Power Query or in a SQL back‑end by merging overlapping periods per Product and Category before loading them into the model.Lead time can then be calculated with a simple DAX expression using SUMX over consolidated intervals.
4. Summary of Both Approaches
Option A – DAX solution (suitable for moderate volumes)- Normalise category columns into a bridge table.
- Use a Date table.
Calculate lead time by counting distinct active days.
Option B – Back‑end consolidation (suitable for large volumes)- Merge overlapping date intervals per Product and Category before loading to Power BI.
Use simple DAX to sum the lengths of consolidated intervals.
DISCLAIMER: While I wrote a draft of this answer, I used Copilot to create a longer, more detailed step-by-step description to make it easier to apply.-
Hi Hichamas4
You are facing a scenario where products may belong to multiple categories (Bike, Car, Truck), and where each product-category combination has associated date intervals. Your objective is to calculate lead time in days per category while applying three conditions:
Categories left blank must be ignored.
Overlapping date intervals must not be counted twice.
Gaps (periods where no interval exists) must not contribute to the total lead time.
This is fundamentally an “interval-union” problem, where multiple date ranges per product and category need to be treated as a single continuous timeline.
1. Recommended Data Modelling Approach
Using separate columns for categories (Group-Bike, Group-Car, Group-Truck) complicates the calculations.
A more suitable modelling approach is to normalise the categories by creating a bridge table.
A more suitable modelling approach is to normalise the categories by creating a bridge table.
Proposed structure
-
Dim_Product
ProductID, ProductName -
Bridge_ProductCategory
ProductID, Category
(One row per category, for example Bike, Car or Truck)
Fact_ProductTransactions
ProductID, StartDate, EndDate
ProductID, StartDate, EndDate
This structure allows analytical filtering by category without dealing with blank columns, and avoids complex conditional logic.
Power Query transformation (conceptual steps)
Duplicate the raw product table.
Unpivot the three category columns.
Remove rows where the category value is blank.
Produce a table containing only ProductID and Category.
This becomes your bridge table linking products to categories.
2. Lead Time Calculation Strategy
The most robust method for handling overlapping intervals and gaps is to use a Date dimension and count the number of distinct days for which at least one interval is active.
Because a day is counted only once, overlapping intervals do not accumulate duplicate lead-time.
Days without an interval automatically represent gaps and are excluded.
Days without an interval automatically represent gaps and are excluded.
Required tables
Dim_Date
Fact_ProductTransactions
Dim_Product
Bridge_ProductCategory
Dim_Category
Lead Time Measure (DAX):
Leadtime Days :=
VAR ActiveDates =
FILTER(
ALL('Dim_Date'[Date]),
CALCULATE(
COUNTROWS('Fact_ProductTransactions'),
'Dim_Date'[Date] >= 'Fact_ProductTransactions'[StartDate]
&& 'Dim_Date'[Date] <= 'Fact_ProductTransactions'[EndDate]
) > 0
)
RETURN
COUNTROWS(ActiveDates)
This measure returns the number of days in which at least one interval is active in the current filter context (Product, Category, etc.). Overlaps and gaps are handled implicitly.
3. When to Move Logic to the Back‑End
If the volume of data is high, or if the Date dimension spans many years, the DAX approach may become slow.
In that case, it is advisable to consolidate intervals in Power Query or in a SQL back‑end by merging overlapping periods per Product and Category before loading them into the model.
In that case, it is advisable to consolidate intervals in Power Query or in a SQL back‑end by merging overlapping periods per Product and Category before loading them into the model.
Lead time can then be calculated with a simple DAX expression using SUMX over consolidated intervals.
4. Summary of Both Approaches
Option A – DAX solution (suitable for moderate volumes)
- Normalise category columns into a bridge table.
- Use a Date table.
Calculate lead time by counting distinct active days.
Option B – Back‑end consolidation (suitable for large volumes)
- Merge overlapping date intervals per Product and Category before loading to Power BI.
Use simple DAX to sum the lengths of consolidated intervals.
DISCLAIMER: While I wrote a draft of this answer, I used Copilot to create a longer, more detailed step-by-step description to make it easier to apply.