Advanced DAX vs MS-SQL: Where to Put Your Business Logic for Maintainable Power BI Models

Scenario: The Sales Analytics Model That Got Away

You inherit a Sales Analytics dataset:

• Import mode model
• Source: a SQL Server data warehouse
• Fact tables: FactSales, FactBudget
• Dimensions: DimDate, DimCustomer, DimProduct, DimSalesOrg

The team has grown the model for a couple of years:

• 200+ measures, many overlapping (e.g. Sales, Sales_Adj, Sales_Final)
• Complex DAX to handle:
  • Customer-specific discount rules
  • Region-specific fiscal calendars
  • Data quality fixes (e.g. excluding certain products, reassigning customers)
• MS-SQL layer mostly doing basic ETL and keys; business logic pushed into DAX because “it’s easier to change”.

Symptoms:

• New analysts are afraid to touch measures; nobody is sure which definition is “official”.
• Performance issues on larger reports; some visuals take several seconds to refresh.
• Conflicting numbers between Power BI and other SQL-based reports.

We’ll use this scenario as the anchor while we walk through where logic belongs.

Types of Business Logic: Classify Before You Place

Before arguing DAX vs MS-SQL, classify the logic. For this scenario, the key categories:

1. Data quality and conformance

   • Standardising codes
   • Deduplicating entities
   • Fixing historical data errors

2. Structural logic

   • Surrogate keys
   • Slowly changing dimensions (SCD)
   • Snapshot fact tables

3. Business rules and metrics

   • KPI definitions (e.g. Net Sales, Margin, Churn)
   • Filters that define “active customer”, “core product”, etc.

4. User-facing calculation logic

   • Time intelligence (YoY, MTD, rolling periods)
   • Aggregations and ratios over user selections
   • Dynamic behaviour based on slicers

You need different tools for each. Trying to do all of them in DAX or all of them in MS-SQL is what breaks maintainability.

What MS-SQL Is Actually Good At for Power BI

From the SQL Server side, you typically have:

• Tables and views (including indexed views where appropriate)
• Stored procedures (if you’re using them for ETL or feeding DirectQuery)
• Materialised structures (e.g. pre-aggregated tables)

For an Import mode Power BI model, the most relevant are views and pre-shaped tables.

Put These in MS-SQL

1. Data quality and conformance

   • Fixing missing or invalid values
   • Mapping legacy codes to canonical values
   • Unifying entities (e.g. merging duplicate customers)

   Example: standardising customer status codes.

   CREATE VIEW dbo.vDimCustomer AS
   SELECT
       c.CustomerKey,
       c.CustomerCode,
       c.CustomerName,
       CASE
           WHEN c.StatusCode IN ('A', 'ACTIVE', '1') THEN 'Active'
           WHEN c.StatusCode IN ('I', 'INACTIVE', '0') THEN 'Inactive'
           ELSE 'Unknown'
       END AS CustomerStatus,
       c.RegionCode,
       c.CreatedDate
   FROM dbo.DimCustomer c;
   

   This view gives Power BI a clean, canonical CustomerStatus instead of pushing that mapping into DAX measures.

2. Structural logic and relationships

   • Building proper dimension tables with surrogate keys
   • Handling SCD (e.g. Type 2) so Power BI can use snapshot facts reliably
   • Ensuring referential integrity where the warehouse allows it

   Example: SCD Type 2 customer dimension exposed via a view that selects the current version.

   CREATE VIEW dbo.vDimCustomerCurrent AS
   SELECT
       c.CustomerKey,
       c.CustomerCode,
       c.CustomerName,
       c.CustomerStatus,
       c.RegionCode
   FROM dbo.DimCustomer c
   WHERE c.IsCurrent = 1;
   

3. Heavy joins and pre-aggregation for stable metrics

   • Complex joins that are stable and not user-driven
   • Base-level aggregations that will be reused by many reports

   Example: pre-aggregated net sales by customer and month.

   CREATE VIEW dbo.vFactSalesMonthly AS
   SELECT
       s.CustomerKey,
       s.DateKey,
       SUM(s.GrossAmount - s.DiscountAmount) AS NetSalesAmount
   FROM dbo.FactSales s
   GROUP BY
       s.CustomerKey,
       s.DateKey;
   

   This view becomes a fact table in Power BI for many metrics; DAX then builds ratios and time intelligence on top.

Why This Belongs in MS-SQL

• Consistency: Other tools (SSRS, ad-hoc SQL) see the same logic.
• Performance: SQL Server is optimised for joins, filtering and aggregation at scale; Power BI benefits from cleaner, smaller tables.
• Governance: Changes to core business logic can be reviewed and versioned in the database layer.

In our Sales Analytics scenario, moving status standardisation, SCD handling and base net sales aggregation into views immediately reduces measure complexity and eliminates several conflicting measures.

What Advanced DAX Is Actually Good At

In the Power BI semantic model, DAX operates over:

• Columnar, in-memory tables (Import) or remote tables (DirectQuery / composite)
• Relationships defined in the model
• User selections (filters, slicers, row context)

Put These in DAX

1. User-facing metrics and KPIs

   • Anything that needs to respond to filters and slicers
   • Ratios, rankings, and dynamic classifications

   Example: Net Sales measure using the pre-aggregated view.

   Net Sales :=
   SUM ( vFactSalesMonthly[NetSalesAmount] )
   

   If you don’t have pre-aggregation in SQL, you can still do it in DAX:

   Net Sales (from FactSales) :=
   SUMX (
       FactSales,
       FactSales[GrossAmount] - FactSales[DiscountAmount]
   )
   

2. Time intelligence

   • Year-over-year, month-to-date, rolling 12 months
   • Calculations dependent on the model’s date table and relationships

   Example: YoY Net Sales using a proper marked date table.

   Net Sales YoY :=
   VAR CurrentPeriod = [Net Sales]
   VAR PreviousPeriod =
       CALCULATE (
           [Net Sales],
           DATEADD ( DimDate[Date], -1, YEAR )
       )
   RETURN
       DIVIDE ( CurrentPeriod - PreviousPeriod, PreviousPeriod )
   

3. Dynamic business rules based on filter context

   • “Core customer” defined by user-selected time window
   • “Top N” logic depending on slicers

   Example: active customer in the last 90 days based on user filter context.

   Active Customers (90d) :=
   VAR Last90Days =
       DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -90, DAY )
   RETURN
       DISTINCTCOUNT (
           CALCULATETABLE (
               FactSales[CustomerKey],
               Last90Days
           )
       )
   

   This kind of logic is hard to express once-and-for-all in SQL, because it depends on the current filter context. DAX is built for it.

Why This Belongs in DAX

• Interactivity: DAX sees the report’s filter context; SQL doesn’t.
• Reusability in the semantic model: Measures can be reused across visuals and reports connected to the same dataset.
• Separation of concerns: The semantic model is the right place for user-facing definitions; the warehouse is the right place for data structure and quality.

In our Sales Analytics scenario, moving dynamic “active customer” definitions and time intelligence into DAX while keeping structural status logic in SQL makes definitions clearer and easier to maintain.

Advanced DAX vs MS-SQL: A Decision Framework

Instead of rules-of-thumb like “do everything in DAX” or “push everything down”, use a simple decision framework.

For each piece of logic, ask:

1. Is the logic tied to user selections or report context?

   • Yes → Prefer DAX.
   • No → Consider MS-SQL.

2. Does the logic define core data structure or quality?

   • Yes → Prefer MS-SQL.

3. Is the logic reused across multiple tools, not just Power BI?

   • Yes → Prefer MS-SQL.

4. Is the logic simple but context-dependent (filters, time)?

   • Yes → Prefer DAX.

5. Is performance currently a problem, and is this logic heavy on joins or row operations?

   • Yes → Consider pushing more of it into MS-SQL, especially for Import models.

Let’s apply this to three painful measures from our scenario.

Example 1: Customer Status Logic in DAX (Bad Fit)

Existing measure:

Net Sales Active Customers :=
CALCULATE (
    [Net Sales],
    KEEPFILTERS (
        FILTER (
            DimCustomer,
            DimCustomer[StatusCode] IN { "A", "ACTIVE", "1" }
        )
    )
)

Problems:

• Status mapping is duplicated in multiple measures.
• Other tools using DimCustomer don’t see the mapping.
• Harder to change when codes evolve.

Refactor:

• Move status mapping into vDimCustomer in SQL (as shown earlier).
• Use a simple DAX filter:

Net Sales Active Customers :=
CALCULATE ( [Net Sales], DimCustomer[CustomerStatus] = "Active" )

Example 2: Fixed 90-Day Activity Window in SQL (Bad Fit)

Existing SQL view:

CREATE VIEW dbo.vActiveCustomer90d AS
SELECT DISTINCT s.CustomerKey
FROM dbo.FactSales s
WHERE s.InvoiceDate >= DATEADD(DAY, -90, CAST(GETDATE() AS date));

Used in Power BI to filter DimCustomer via a relationship.

Problems:

• “Last 90 days” is fixed relative to the server date, not report filters.
• Users can’t change the window without new views.

Refactor:

• Remove the activity window from SQL; keep only raw sales.
• Implement activity logic in DAX (as shown in the Active Customers (90d) measure).

Example 3: Complex Join Logic in DAX (Often a Bad Fit)

Existing DAX calculated table:

Sales with Budget :=
SUMMARIZECOLUMNS (
    DimCustomer[CustomerKey],
    DimProduct[ProductKey],
    DimDate[Date],
    "Sales", [Net Sales],
    "Budget", [Budget Amount]
)

Problems:

• Complex calculated table logic in DAX is harder to debug than a SQL view.
• If the join conditions change (e.g. new budget allocation logic), you have to modify DAX instead of centralising it.

Refactor:

• Build a vFactSalesBudget view in SQL joining FactSales and FactBudget with the agreed business rules.
• Import that view into Power BI and keep DAX focused on measures.

Special Considerations: Import vs DirectQuery / Composite Models

The storage mode affects where logic can realistically live.

Import Models

• Data is cached in-memory in the VertiPaq engine.
• Complex DAX over well-shaped tables is usually fast.
• You have flexibility: heavy logic can be either in SQL or DAX.

Practical guidance:

• Use SQL for data shaping and conformance.
• Use DAX for interactive metrics and time intelligence.
• Avoid calculated columns that replicate SQL logic; prefer pushing that down.

DirectQuery and Composite Models

• DAX queries translate into SQL and are executed against the source.
• Complex DAX can generate inefficient SQL, depending on the pattern and source.
• Latency and concurrency constraints are more visible.

Practical guidance:

• Push more logic into well-designed SQL views or stored procedures where appropriate.
• Keep DAX measures relatively simple; avoid patterns that generate many subqueries.
• Be explicit about which tables are Import vs DirectQuery in composite models and place heavy logic on Import tables when possible.

In our Sales Analytics scenario, if the dataset moves to DirectQuery for near-real-time data, more of the heavy joining and pre-aggregation should move to MS-SQL views to avoid slow visuals.

Governance: Making Logic Discoverable and Auditable

The biggest maintainability gains come from making logic discoverable.

Practical steps for the scenario team:

1. Define a single source for each core concept

   • "Net Sales" definition lives in one MS-SQL view or one DAX base measure.
   • Build derived measures from that base, not from raw columns.

2. Document where each rule lives

   • Short README in your repo or wiki: for each major KPI, note whether the main logic is in SQL or DAX and where.

3. Use naming conventions

   • SQL views: vDim* for dimensions, vFact* for facts, vKpi* for complex pre-aggregated structures.
   • DAX: reserve suffixes like _Base for base measures and avoid creating multiple near-duplicates.

4. Align SQL and Power BI teams

   • Agree that structural and conformance logic is owned by the database team.
   • Agree that interactive and presentation logic is owned in the semantic model.

After applying this in the Sales Analytics model, the team ends up with:

• Fewer, clearer measures
• A documented mapping between SQL views and DAX measures
• Consistent numbers across Power BI and SQL-based reporting

One Practical Takeaway

When you add or change a business rule in Power BI, decide explicitly: is this a structural, shared definition (move to MS-SQL), or an interactive, user-context-dependent metric (keep in DAX)? Writing that decision down for each major rule will do more for your model’s long-term health than any single optimisation trick.