Power BI Data Modeling & DAX Questions

Understand Power BI's data modeling concepts: relationships (one-to-many, many-to-many), cardinality, filter propagation, and bidirectional relationships. Write DAX formulas for measures and calculated columns, including basic (SUM, COUNT, AVERAGE) and advanced functions (CALCULATE, FILTER, ALL, VALUES). Understand variables and performance implications of DAX formulas.

MediumSystem Design

67 practiced

You must allow users to slice reports by either Order Date or Ship Date simultaneously while preserving correct measures. Propose a data model and DAX pattern that supports both slicers without duplicating measures, mentioning disconnected slicers, USERELATIONSHIP, SWITCH logic, or TREATAS where appropriate.

Sample Answer

Requirements & approach:- Allow users to slice reports by Order Date or Ship Date (either/or) and preserve correct aggregations without duplicating every measure.- Use one canonical Date (calendar) table for time intelligence. Keep the natural relationship Order[OrderDateKey] → Date[DateKey] active; keep Order[ShipDateKey] → Date[DateKey] inactive (or use a disconnected date slicer + TREATAS).- Use a small disconnected “DateSelector” table (values: "Order Date", "Ship Date") as a slicer to let users pick which date to apply. In measures, use SWITCH + USERELATIONSHIP or TREATAS to route the calendar filter to the correct Order column.

Data model summary:- Orders (fact): OrderID, OrderDateKey, ShipDateKey, Revenue, Qty...- Date (dim): DateKey, Date, Year, Month...- DateSelector (disconnected dim): Type = {"Order Date","Ship Date"} — used as a slicer

DAX pattern (example measure for Revenue):

dax

Revenue (By Selected Date) :=
VAR SelectedType = SELECTEDVALUE('DateSelector'[Type], "Order Date")
VAR BaseMeasure = SUM( Orders[Revenue] )
RETURN
SWITCH(
  SelectedType,
  "Ship Date",
    CALCULATE(
      BaseMeasure,
      USERELATIONSHIP( Orders[ShipDateKey], 'Date'[DateKey] )
    ),
  // default: Order Date (active relationship already in model)
    CALCULATE( BaseMeasure )
)

If you prefer range slicers on Date table (user selects a date range on the calendar slicer) and want to map those dates to the chosen order field, use TREATAS to apply the calendar's filter values onto the desired Orders date column:

dax

Revenue (TREATAS pattern) :=
VAR SelectedType = SELECTEDVALUE('DateSelector'[Type], "Order Date")
VAR CalendarDates = VALUES( 'Date'[Date] )
RETURN
SWITCH(
  SelectedType,
  "Ship Date",
    CALCULATE(
      SUM(Orders[Revenue]),
      TREATAS( CalendarDates, Orders[ShipDate] )
    ),
  SUM(Orders[Revenue])  // uses active OrderDate relationship
)

Key points & trade-offs:- USERELATIONSHIP is concise and preserves built-in time intelligence (use when you have inactive relationship). But USERELATIONSHIP only activates a single inactive relationship per CALCULATE and relies on having that inactive relationship defined.- TREATAS is flexible (works with disconnected slicers/ranges and multiple columns) and is explicit about how filters are applied; better when you want to map arbitrary date sets or multiple filters.- Keep measures generic (use one pattern per metric) to avoid duplicating measures per date type.- Ensure visuals use the canonical Date table for consistent axis/formatting; use DateSelector slicer to switch semantics.- Document the pattern for report consumers to avoid confusion.

MediumTechnical

48 practiced

Write a DAX measure to calculate the number of distinct customers who purchased in the last 30 days relative to report context. Model: Sales(CustomerID, SaleDate). The measure should respect slicers like product and geography and be written to perform well on large datasets.

Sample Answer

Use a filter that’s anchored to the current report context (so slicers on product/geography still apply), compute a dynamic 30-day window relative to the context, and count distinct CustomerID. Use CALCULATE with DISTINCTCOUNT and a date filter based on MAX(Sales[SaleDate]) or TODAY() depending on requirements; using MAX keeps it relative to current filter context (e.g., when you have a selected date on a slicer).

dax

Customers_Last30Days :=
VAR EndDate = MAX( Sales[SaleDate] )         // respects date slicer/context
VAR StartDate = EndDate - 29                // inclusive 30-day window
RETURN
CALCULATE(
    DISTINCTCOUNT( Sales[CustomerID] ),
    FILTER(
        ALLSELECTED( Sales ),                // keep slicers (product, geo), but remove row context
        Sales[SaleDate] >= StartDate
        && Sales[SaleDate] <= EndDate
    )
)

Key points:- ALLSELECTED(Sales) preserves external slicers (product, geography) while allowing our date filter to be applied correctly.- Using MAX(SaleDate) makes the window relative to current report/date context (use TODAY() instead if always relative to today).- DISTINCTCOUNT is efficient on large datasets when CustomerID is an integer and properly indexed.Edge cases:- No date in context => EndDate is blank; wrap VAR with IF to return 0.- If SaleDate has time component, consider using DATE() or truncation.

Alternatives:- Use VALUES( DateTable[Date] ) and USERELATIONSHIP if you have a separate Date dimension for better performance and clarity.

MediumSystem Design

63 practiced

You have Sales(OrderDate, ShipDate, OrderID) and Calendar(Date, IsHoliday). Design the data model and write a DAX measure to compute average days to ship excluding weekends and holidays. Describe which relationships you would use and how USERELATIONSHIP factors into the measure.

Sample Answer

Data model- Calendar table (Date PK, IsHoliday boolean, WeekdayNumber, IsWorkday = WeekdayNumber not in (1,7) && NOT IsHoliday).- Sales table (OrderID PK, OrderDate, ShipDate).- Relationships: - Active: Calendar[Date] → Sales[OrderDate] (1:*). This makes most time-sliced visuals default to order date. - Inactive: Calendar[Date] ↔ Sales[ShipDate] (1:*), created but not active. We'll activate it inside measures with USERELATIONSHIP when we need ship-date context.

Measure approach- For each order, count calendar days between OrderDate and ShipDate where Calendar[IsWorkday]=TRUE().- Use SUMX over Sales to aggregate per-order workday counts, then divide by number of orders (or use AVERAGEX).

DAX measure (Average workdays to ship, excluding weekends & holidays)

dax

AverageWorkdaysToShip :=
VAR Orders =
    FILTER( Sales, NOT( ISBLANK( Sales[OrderDate] ) ) && NOT( ISBLANK( Sales[ShipDate] ) ) )

VAR TotalWorkdays =
    SUMX(
        Orders,
        VAR ThisOrderDate = Sales[OrderDate]
        VAR ThisShipDate  = Sales[ShipDate]
        -- Count workdays between the two dates using Calendar table
        RETURN
            CALCULATE(
                COUNTROWS( Calendar ),
                -- Temporarily activate ShipDate relationship so Calendar filters by ShipDate in row-context visuals if needed
                USERELATIONSHIP( Calendar[Date], Sales[ShipDate] ),
                Calendar[Date] >= ThisOrderDate,
                Calendar[Date] <= ThisShipDate,
                Calendar[IsWorkday] = TRUE()
            )
    )

VAR OrderCount = COUNTROWS( Orders )

RETURN
IF( OrderCount = 0, BLANK(), DIVIDE( TotalWorkdays, OrderCount ) )

Why USERELATIONSHIP matters- The model defaults to Calendar → OrderDate. When we COUNTROWS(Calendar) but want Calendar to be evaluated relative to ShipDate (so that any external calendar filters applied to ship-date visuals behave as expected), we use USERELATIONSHIP to temporarily activate the ShipDate relationship inside CALCULATE.- If you always compute workdays by scanning ALL(Calendar) with explicit date-range filters, USERELATIONSHIP isn’t required; but using it keeps the measure responsive to filters where report users choose to filter by ship date rather than order date.

Edge cases & notes- Ensure Calendar covers full date range of Sales.- Handle ShipDate < OrderDate (exclude or treat as 0) — add an IF guard if needed.- For performance at scale, consider a calculated WorkdayCount column or precomputed shipping-workday metric in ETL rather than row-by-row SUMX on millions of rows.

EasyTechnical

72 practiced

Explain the difference between VALUES and DISTINCT in DAX. Provide an example where VALUES returns a BLANK or different result than DISTINCT because of a blank row or current filter context, and explain how that behavior affects measures.

Sample Answer

VALUES returns the distinct values of a column in the current filter context — but it can return a table with a single BLANK row when the current context includes rows where that column is null or when no rows exist; DISTINCT always returns distinct non-duplicated values from the underlying table expression (it behaves like VALUES when passed a column reference but won't return a BLANK-only result in the same way when context is different). Key practical difference: VALUES is context-aware and can return a BLANK row representing “a blank value in the column” or “no rows” (depending on usage), which affects measures that use COUNTROWS(VALUES(...)) or use VALUES for lookup.

Example:

-- Suppose Sales table has rows where ProductID is blank
-- Measure A: distinct products seen in current context
Measure_VALUES = COUNTROWS(VALUES(Sales[ProductID]))

-- Measure B: distinct products using DISTINCT over the table
Measure_DISTINCT = COUNTROWS(DISTINCT(SELECTCOLUMNS(Sales,"P",Sales[ProductID])))

If the current filter context filters to only rows where ProductID is BLANK, VALUES(Sales[ProductID]) returns a one-row table with BLANK, so COUNTROWS(...) = 1. DISTINCT over the SELECTCOLUMNS returns the same here; but if the context has no rows at all (e.g., filtered to a non-existent month), VALUES(Sales[ProductID]) returns an empty table while DISTINCT over the whole column expression might return all distinct values unless you explicitly restrict it — behavior depends on input. More commonly, VALUES used on a lookup table (Products) inside a measure will return BLANK when the single-value expectation fails; for example:-- Using VALUES(Products[Category]) inside a measure that expects a single category can return BLANK when multiple categories are present, causing DIVIDE or LOOKUPVALUE to produce unexpected BLANKs.

Impact on measures:- Measures that rely on a single value (e.g., IF(HASONEVALUE(...) , VALUES(...), ...)) must use HASONEVALUE or ISFILTERED to guard for multi-value context or blanks.- Unchecked use of VALUES can produce BLANK results or count blanks as values, skewing counts and ratios.

Best practices:- Use HASONEVALUE or ISBLANK checks before using VALUES for scalar lookups.- Use DISTINCT when you want distinct set semantics independent of current row context, or use FILTER/REMOVEFILTERS to control context.- Use BLANK handling (COALESCE, IF(ISBLANK(...),0,...)) when blanks should not count.

HardTechnical

65 practiced

Explain how the VertiPaq engine compresses columnar data in Power BI. For a model with many wide textual columns and high-cardinality fields, list concrete steps to reduce memory footprint including data type changes, dictionary encoding considerations, and ETL strategies.

Sample Answer

VertiPaq is a columnar, in-memory storage engine that compresses each column independently using dictionary encoding, run-length encoding, bit-packing and other schemes. It stores distinct values in a dictionary and then replaces actual column values with small integer IDs (surrogate keys). Compression is best when columns have low cardinality, repeated values, or short fixed-size representations.

Concrete steps to reduce memory for many wide textual and high-cardinality fields:

1. Assess cardinality and usage- Identify which columns are used in visuals, filters, relationships, or measures. Drop unused columns.- Measure distinct counts and storage impact (e.g., Power BI Performance Analyzer / VertiPaq Analyzer).

2. Change data types- Convert wide text to numeric or categorical where appropriate (dates → Date type; small numeric strings → integers).- Use fixed-length types: DateTime → Date when time not needed; Decimal precision → smallest necessary scale.- Replace GUIDs with integer surrogate keys when used as keys.

3. Dictionary encoding considerations- For high-cardinality text used only for display (not grouping/filters), move to a separate lookup table keyed by integer ID; store ID in fact table.- Pre-hash long strings only if hashing preserves equality semantics; avoid collisions—prefer surrogate IDs.- Combine sparsely used string columns into a single lookup with attributes to reduce repeated dictionaries.

4. ETL strategies- Trim whitespace, normalize casing, and truncate unnecessary suffixes at ETL to increase dictionary reuse.- Tokenize or split large free-text into flags/keywords rather than storing full text.- Aggregate/detail: pre-aggregate at ETL for common reporting grain to reduce row counts.- Load heavy textual columns only into DirectQuery or a hybrid model (composite model) if they’re rarely needed interactively.

5. Modeling practices- Use star schema: move descriptive text to dimension tables; facts should contain compact keys.- Disable auto date/time if not needed.- Reduce column cardinality by bucketing continuous values into bins when exact value not needed.

6. Validate and iterate- After changes, re-run size analysis to confirm memory reduction and check query performance trade-offs.

Trade-offs: converting to integers and lookup tables increases ETL complexity and joins but massively improves VertiPaq compression and query speed. When real-time text is essential, consider DirectQuery or Azure Cognitive Search for full-text needs.

Unlock Full Question Bank

Get access to hundreds of Power BI Data Modeling & DAX interview questions and detailed answers.

Join thousands of developers preparing for their dream job.