Power BI Advanced Features and DAX Questions

Focuses on advanced Power BI capabilities and deep proficiency in the DAX expression language and data modeling specific to Power BI. Includes writing and optimizing DAX measures and calculated columns using functions such as CALCULATE, FILTER, SUMX, and advanced time intelligence functions like DATEADD and year to date patterns. Covers data model optimization principles including star schema design, relationship management, and reducing cardinality for the VertiPaq engine. Addresses query folding, DirectQuery versus Import mode trade offs, incremental refresh configuration, row level security, query diagnostics and performance profiling, and techniques to tune DAX and model performance for large datasets.

MediumTechnical

44 practiced

List and explain practical strategies to reduce column cardinality in a Power BI model (e.g., for a high-cardinality DeviceID or Email). Include bucketization, hashing, surrogate keys, splitting fields, or moving to DirectQuery. For each strategy describe pros/cons and impact on analytics.

Sample Answer

For high-cardinality columns (DeviceID, Email) in Power BI/VertiPaq, practical strategies:

1) Bucketization / grouping- What: map many distinct values into buckets (e.g., device types, usage bands, hashed buckets).- Pros: massively reduces distinct values; improves compression and performance.- Cons: loses granularity; downstream detail-level queries impossible.- Impact: good for trend/aggregation analysis; not for per-device troubleshooting.

2) Hashing / deterministic hash codes- What: replace long strings with fixed-size hash (e.g., MD5/XXHash) stored as integer/binary.- Pros: smaller storage, faster joins; preserves uniqueness probabilistically.- Cons: risk of collisions (low if 64-bit); hashed values not human-readable.- Impact: retains per-entity uniqueness for aggregations; losing original readable key may require lookups for drillthrough.

3) Surrogate keys (conformed integer IDs)- What: map strings to sequential integer IDs in lookup table.- Pros: best compression and join performance; supports survivors/slowly changing dims.- Cons: needs ETL and key management; extra lookup table increases model complexity.- Impact: preserves one-to-one mapping and supports drilldown via lookup tables.

4) Split fields / normalize- What: decompose emails (local, domain) or device IDs into components.- Pros: reduces distinctiveness of individual columns; enables domain-level analysis.- Cons: requires design effort; may not eliminate cardinality if components remain unique.- Impact: enables aggregated insights (e.g., domain trends) while keeping details in separate storage.

5) Move to DirectQuery / composite model- What: keep high-cardinality data in source DB; query at runtime.- Pros: avoids VertiPaq cardinality limits; real-time data.- Cons: higher query latency, limited DAX & modeling features, load on source.- Impact: suitable for detail-heavy drillthroughs; not ideal for fast interactive aggregated dashboards.

6) Other practical steps- Remove unused columns; apply data types (integers) and summarize/aggregate in ETL; use incremental loads and column encoding settings.- Trade-off summary: choose surrogate keys/hashing for performance + preserved uniqueness; bucketization/splitting for aggregate-focused reports; DirectQuery when detail must remain in source despite latency.

EasyTechnical

44 practiced

Compare DirectQuery and Import modes in Power BI. For each mode, list pros and cons relevant to latency, model size, refresh cadence, DAX function availability, and query performance. Give an example scenario (real-time dashboard vs historical analysis) and justify which mode you'd pick.

Sample Answer

DirectQuery vs Import — concise comparison for a BI analyst

Import mode (data loaded into Power BI model)Pros:- Latency: Very low interactive latency — queries served from in-memory columnstore.- Model size: Limited by Power BI capacity / dataset limits; large historical models may need aggregation tables.- Refresh cadence: Requires scheduled/full incremental refresh; near real-time needs frequent refreshes (limited by service).- DAX availability: Full DAX function set and optimal performance for complex measures.- Query performance: Fast for well-designed models (star schema, proper indexing/aggregations).

Cons:- Latency to source changes: Data is stale between refreshes.- Model size constraints: Large raw tables can hit limits or increase cost.

DirectQuery mode (queries live to source)Pros:- Latency: Near-real-time data — reflects source instantly.- Model size: Minimal in-Power BI footprint; good for very large source tables.- Refresh cadence: No dataset refresh needed for data freshness (metadata still cached).- Model size: Supports large datasets without importing.

Cons:- Interactive latency: Slower visuals — dependent on source performance and network.- DAX availability: Some DAX functions/features restricted; certain time intelligence patterns less efficient.- Query performance: Heavily dependent on source indexes, query folding, and concurrency limits; more risk of timeouts.

Which to pick — examples- Real-time operational dashboard (e.g., monitoring orders on a trading floor): Choose DirectQuery. Justification: requires up-to-the-second data; accept slower visuals and optimize source (indexes, dedicated reporting DB) and use aggregations or composite models to improve performance.- Historical trend analysis (e.g., multi-year sales performance): Choose Import. Justification: fast interactivity for ad-hoc analysis, full DAX capability, and efficient aggregation; use incremental refresh to keep dataset manageable.

Hybrid option: Composite model (Import + DirectQuery for large/detail tables) combines best of both—import aggregates for speed, direct query to detail for recency. Use when both interactivity and freshness matter.

HardTechnical

25 practiced

Write an optimized DAX expression that returns the Top 5 Customers by Rolling 30-Day Sales. Use SUMMARIZECOLUMNS (or equivalent) and explain why SUMMARIZECOLUMNS often performs better than SUMMARIZE in modern tabular models. Include how you would limit the result to the visual context.

Sample Answer

Approach: build a rolling 30‑day sales measure, summarize customers with that measure using SUMMARIZECOLUMNS (which avoids context transition and uses the storage engine efficiently), then take TopN(5) and respect the visual filter context by applying VALUES or KEEPFILTERS on customer selection.

dax

// Rolling 30-day Sales measure (reusable)
Rolling30Sales :=
VAR MaxDate = MAX('Date'[Date])
RETURN
CALCULATE(
    [Total Sales],                              -- base measure: SUM('Sales'[Amount])
    DATESBETWEEN('Date'[Date], MaxDate - 29, MaxDate)
)

// Top 5 customers by Rolling30Sales (table)
Top5Customers_Rolling30 :=
VAR CustomersTable =
    SUMMARIZECOLUMNS(
        'Customer'[CustomerKey],
        'Customer'[CustomerName],
        "R30Sales", [Rolling30Sales],
        KEEPFILTERS(VALUES('Customer'[CustomerKey]))  // keeps visual filters on customers
    )
VAR Top5 = TOPN(5, CustomersTable, [R30Sales], DESC)
RETURN
Top5

Key points:- SUMMARIZECOLUMNS pushes grouping to the storage engine, avoids implicit context transition that SUMMARIZE may cause, and works better with KEEPFILTERS and query folding—resulting in faster, more predictable queries.- Use KEEPFILTERS or VALUES to honor visual filters (slicers) and prevent accidentally overriding them.- For visuals, return the table measure or use RANKX over SUMMARIZECOLUMNS if you need a Boolean/top flag.

Edge cases:- Null/no sales dates: ensure Date table is contiguous and marked as Date.- Large customer cardinality: pre-aggregate if necessary.

Alternatives:- Use RANKX on ALLSELECTED('Customer') if you must preserve more complex visual context; but SUMMARIZECOLUMNS + TOPN is usually faster.

HardTechnical

27 practiced

Demonstrate how replacing nested EARLIER/EARLIEST patterns with variables (VAR) and modern DAX constructs improves readability and performance. Provide an example where EARLIER was used to compute a running rank and then refactor it to use variables and more efficient functions.

Sample Answer

Approach:Replace nested EARLIER/EARLIEST row-context tricks with VAR to capture values once, and use table functions (FILTER, ADDCOLUMNS, RANKX) that work in filter-context. This removes repeated row-context iteration, improves readability, and often gives better performance.

Original (using EARLIER) — computes running rank per Category by SalesAmount:

dax

RunningRank_Earlier =
SUMX(
  FILTER(
    Sales,
    Sales[Category] = EARLIER(Sales[Category]) &&
    (Sales[SalesDate] < EARLIER(Sales[SalesDate]) ||
     (Sales[SalesDate] = EARLIER(Sales[SalesDate]) && Sales[SalesID] <= EARLIER(Sales[SalesID])))
  ),
  1
)

Refactored (using VAR + RANKX for clarity and efficiency):

dax

RunningRank_VAR =
VAR CurrentCategory = Sales[Category]
VAR CurrentDate = Sales[SalesDate]
VAR CurrentID = Sales[SalesID]
VAR CategoryTable =
    FILTER(
        ALL(Sales),
        Sales[Category] = CurrentCategory
    )
VAR Ranked =
    RANKX(
        CategoryTable,
        // tie-breaker: date then ID to make deterministic ordering
        Sales[SalesDate] + Sales[SalesID] / 1000000,
        CurrentDate + CurrentID / 1000000,
        ASC,
        DENSE
    )
RETURN
Ranked

Key points / reasoning:- VAR captures current row values once, avoiding nested EARLIER calls which are hard to read.- ALL(Sales) in CategoryTable ensures RANKX evaluates over the full category regardless of existing row-context filters; adjust depending on desired behavior.- Using RANKX delegates iteration to an optimized engine function, clearer intent (rank) and usually faster than manual SUMX+FILTER loops.- Tie-breaker numeric expression ensures deterministic ordering; adjust precision as needed.

Performance & maintainability:- Readability: variables name intent (CurrentCategory/CurrentDate) versus multiple EARLIER calls.- Performance: RANKX and using VAR to limit filter scope reduces repeated evaluation; avoids nested row-context which can cause complex evaluation paths.- Edge cases: duplicate dates—use stable tie-breaker; very large tables—consider pre-aggregating or creating indexed lookup tables.- Alternatives: ADDCOLUMNS + RANKX on a summarized table (SUMMARIZE/ADDCOLUMNS) if you need ranking at an aggregate grain instead of row-level.

HardTechnical

26 practiced

A customer-email column with 50M unique values is causing your model to exceed memory limits. Propose and justify a concrete plan to reduce memory footprint while maintaining analytic capability: include surrogate keys, bucketing, hashing, moving column to a separate DirectQuery table, and trade-offs for each option.

Sample Answer

Situation: A fact table contains a customer_email column with ~50M unique values; loading it into VertiPaq causes memory exhaustion and slow refreshes.

Plan (concrete steps):1. Measure current impact- Profile column: distinct count, nulls, percent used in visuals/filters, and relationships.- Track model size contribution (VertiPaq analyzer or Power BI Desktop Performance Analyzer).

2. Preferred long-term solution: surrogate key + dimension table- Create customer_dim table with surrogate_customer_id (int, clustered PK) and customer_email stored once.- Replace email in fact with surrogate_customer_id (INT, 4 bytes) via ETL.- Benefits: Big memory reduction (int vs high-cardinality string), preserves full email for lookups, allows indexing, faster joins.- Trade-offs: ETL complexity, need to maintain dimension (slowly changing dimensions, merges), extra join in visuals (negligible in VertiPaq).

3. If ETL change is expensive: hashing or dictionary-encoding- Hash email to 64-bit integer (e.g., xxHash64) stored in model.- Benefits: Smaller footprint, no separate table.- Trade-offs: Possible hash collisions (extremely low with 64-bit), loss of original string unless stored separately; hashed values are opaque for business users.

4. Bucketing / grouping- For analytics that don't need per-email granularity, bucket emails into meaningful groups (customer_type, domain, region) during ETL.- Benefits: Keeps analytical capability for aggregate trends, dramatic cardinality reduction.- Trade-offs: Loses per-email detail; not suitable for tasks needing exact email-level joins.

5. Move to DirectQuery / composite model- Move email column (or entire customer_dim) to DirectQuery-only table; keep numeric measures in Import mode.- Benefits: Offloads storage to SQL engine, supports ad-hoc lookups.- Trade-offs: Slower slicers/filters due to live queries, limited modeling features, dependency on DB performance and network.

Recommendation and validation:- Implement surrogate keys + small customer_dim as primary; for immediate relief, apply hashing for rapid deployment.- Benchmark: compare model size, refresh time, and dashboard responsiveness for each option. Verify no collisions (if hashing) and correctness of joins.- Monitor: implement incremental refresh and SCD handling for customer_dim to maintain analytic integrity.

This combination balances memory reduction, maintainability, and analytic capability while enabling fallback to DirectQuery for rarely-used string-level lookups.

Unlock Full Question Bank

Get access to hundreds of Power BI Advanced Features and DAX interview questions and detailed answers.

Join thousands of developers preparing for their dream job.