Day 24: Power BI — Technical Interview Q&A Set 1

A DAX measure filter (via CALCULATE) is embedded in the measure definition and always applies when that measure is evaluated — the report user cannot override it. A visual-level filter is set in the Filters pane and applies to one specific visual — the report developer sets it but it does not affect the underlying measure, only which rows of data the visual queries. Example: a measure defined as CALCULATE(SUM(Revenue), Region = "North") always shows North revenue regardless of any slicer. A visual-level filter for Region = "North" filters the visual but a different measure on the same visual responds to slicers normally.

Option 1 — Bridge table: create an intermediary table that has one row per combination of the two many-side keys. The bridge table has a 1:* relationship with each original table, converting the M:M into two 1:M relationships. Recommended approach. Option 2 — Bidirectional relationship: set the relationship to Both cross-filter direction. Power BI handles M:M with bidirectional filtering. Simpler but can cause ambiguous filter paths and unexpected results in complex models. Option 3 — Concatenated key: combine both keys into a single unique key in both tables if there is a meaningful composite key. Use a bridge table whenever possible — it is the most robust solution.

USERELATIONSHIP(column1, column2) activates an inactive relationship for the duration of a CALCULATE expression. Power BI allows only one active relationship between two tables but you can have multiple inactive relationships. Example: a Sales table has both OrderDate and DeliveryDate. You create an active relationship on OrderDate to the Date table. You create an inactive relationship on DeliveryDate to the Date table. To calculate revenue by delivery date: Delivery Revenue = CALCULATE(SUM(Sales[Revenue]), USERELATIONSHIP(Sales[DeliveryDate], 'Date'[Date])). The inactive relationship activates only inside this measure.

COUNTROWS(table) counts the number of rows in a table — the table can be filtered using CALCULATE or FILTER. COUNT(column) counts non-blank values in a specific column. COUNTA(column) counts non-blank values including text. COUNTBLANK(column) counts blank values. For most analyst use cases: COUNTROWS with CALCULATE is the preferred approach because it is more explicit — you are clearly counting rows of a specific filtered table. COUNT(column) can give misleading results if the column has nulls. DISTINCTCOUNT(column) counts unique non-blank values — use for counting unique customers, products, etc.

In DAX, variables (VAR) can store any expression result including measure evaluations: Profit Margin = VAR TotalRevenue = SUM(Sales[Revenue]) VAR TotalCost = SUM(Sales[Cost]) VAR Profit = TotalRevenue - TotalCost RETURN DIVIDE(Profit, TotalRevenue) Variables in DAX are evaluated once in the filter context at the point they are declared — not re-evaluated. This makes complex DAX measures both more readable and more efficient. VAR/RETURN is the modern DAX standard — use it in all measures with more than one calculation step.