calculations either in data lake or power bi

[powerbiexpert22]

in case of using a data lake as the source for Power BI, where is the best place to implement calculations considering reusability, performance optimization and governance perspective? should calculations be handled in the data lake by creating a curated layer, or it should be implemented in Power Query or semantic model within Power BI?

[stoic-harsh · Accepted solution]

Hey @powerbiexpert22,

Fully agree with @pankajnamekar25, and here's how you can break it down:

• Data Lake (Gold Layer)
  • Best for calculations/logic defined once and consumed across multiple reports.
  • Static, reusable, governance-critical logic.
• Power Query 
  
  • Best for re-structuring, early filtering, renaming, type casting columns.
  • Avoid business logic here. It's not reusable across models.
• DAX 
  • Best for logic that depends on report context (slicers, filters, relationships).
  • Interactive, context-aware calculations.

As a general rule of thumb, push everything as far upstream as possible. If a calculation can live in the Data Lake, it should. Only bring logic into Power Query or DAX when it genuinely needs to be dynamic or report-specific.

Best,

Harshit

[v-pnaroju-msft]

Hi @powerbiexpert22,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thankyou, @pankajnamekar25, @stoic-harsh, @cengizhanarslan, @luisoliveira89 and @m4ni for your responses.

Hi @powerbiexpert22,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solutions provided by @pankajnamekar25, @stoic-harsh, @cengizhanarslan, @luisoliveira89 and @m4ni to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[m4ni]

@powerbiexpert12 - Agree with all responses here.  All heavy logic and aggregations should reside as far upstream to the source as possible (Lakehouse/warehouse).  Power BI should only pick up light work or anything which is truely report level specific.  This will promote performance, reusability and governance.

[luisoliveira89]

Hi @powerbiexpert22 

Just following our collegues here: In most cases, the best place is the Lakehouse curated layer (or warehouse/lakehouse transformation layer), especially for reusability, governance, and scalability.

If calculations are implemented in Power BI (Power Query or DAX), they usually become report-specific and end up duplicated across multiple models and reports, which makes maintenance harder over time.

By moving business logic to the curated layer:

• you centralize the logic in one place
• multiple reports and semantic models can reuse the same trusted data
• transformations are processed upstream instead of repeatedly inside Power BI
• it improves consistency across the organization
• it typically scales better for large datasets

Power BI should ideally focus more on presentation logic and analytical measures that are truly report-specific.

Best,

Luis Oliveira

[cengizhanarslan · Accepted solution]

Calculations that are shared across multiple consumers, require heavy joins or aggregations, or represent a single source of truth for business definitions belong in the data lake gold layer — this is the governance layer and changes propagate to all downstream systems automatically. Power Query should be kept thin, handling only lightweight shaping specific to the semantic model such as type corrections, renaming, and row filtering — anything requiring complex joins or reusable logic belongs upstream. DAX in the semantic model is the right layer for business metrics, KPIs, time intelligence, and anything that depends on filter context or user interaction, since these calculations cannot be pre-computed and must respond dynamically to slicers and report filters.

[stoic-harsh · Accepted solution]

Hey @powerbiexpert22,

Fully agree with @pankajnamekar25, and here's how you can break it down:

• Data Lake (Gold Layer)
  • Best for calculations/logic defined once and consumed across multiple reports.
  • Static, reusable, governance-critical logic.
• Power Query 
  
  • Best for re-structuring, early filtering, renaming, type casting columns.
  • Avoid business logic here. It's not reusable across models.
• DAX 
  • Best for logic that depends on report context (slicers, filters, relationships).
  • Interactive, context-aware calculations.

As a general rule of thumb, push everything as far upstream as possible. If a calculation can live in the Data Lake, it should. Only bring logic into Power Query or DAX when it genuinely needs to be dynamic or report-specific.

Best,

Harshit

[pankajnamekar25]

Hello @powerbiexpert22 

Static + reusable logic  Data Lake

Interactive + context-based logic DAX in Power BI

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.

Thanks,

Connect with me on:
LinkedIn |
Data With Pankaj - YouTube