LBL or Aggregation Upstream

[OldDinosaur79]

I’m modelling lifecycle events in Power BI where each record has:

• Created Date

• Closed Date

• Status (Open/Closed)

From these fields, the required metrics are:

• Created (derived from Created Date)

• Closed (derived from Closed Date + Status)

• Outstanding (derived from Status + point‑in‑time logic)

Two approaches are being proposed:

 

Option A — Star schema + LBL dimension + measures

Use a fact table with the raw lifecycle fields, a Date dimension, and measures to calculate Created/Closed/Outstanding. LBL (label) dimension used for drill‑down. Logic lives in the semantic model.

 

Option B — Upstream aggregated table

Upsteam process produces a daily aggregated table with Created/Closed/Outstanding already calculated. Power BI imports the aggregated table and uses LBL for drill‑down.  Told this to avoid complex DAX and reuse upstream compute.

 

Question:

For lifecycle event modelling, is best practice to:

1. Keep raw lifecycle fields and calculate metrics via measures (with LBL), or

2. Pre‑aggregate these metrics upstream and import the aggregated table?

Looking for guidance based on modelling best practice

[OldDinosaur79]

Thank you very much. Thats makes sense. 

[krishnakanth240]

You are welcome @OldDinosaur79 

[krishnakanth240 · Accepted solution]

Hi @OldDinosaur79 

For lifecycle event modelling, Option A (star schema with raw lifecycle data and measures) is preferred approach. Keeping Created Date, Closed Date, and Status at the lowest grain supports changing business requirements and aligns with dimensional modelling. 

Measures can then calculate Created, Closed, and Outstanding using Date dimension and filter context. 

Consider Option B (pre-aggregated data) only if you are having very large volume, performance requirement and need to standardize calculations across multiple downstream consumers