Power Bi Dax Common Patterns Practice Exam

Question 1 Power Bi Dax Common Patterns

Remove all filters from a column?

Avoid wrapping iterators inside CALCULATE unless needed; can cause expensive row→filter transitions on large tables. Fewer joins, simpler measures, faster engine. Replace snowflake by flattening dimensions. YTD Sales := CALCULATE([Total Sales], DATESYTD('Date'[Date])) CALCULATE([Total Sales], ALL(Products[Category]))

Question 2 Power Bi Dax Common Patterns

When to use ALL vs FILTER(ALL(...), ...)?

NewCust := CALCULATE(DISTINCTCOUNT(Sales[CustomerId]), FILTER(ALL(Customers), Customers[FirstPurchaseDate] >= MIN('Date'[Date]) && Customers[FirstPurchaseDate] <= MAX('Date'[Date])))

ALL alone removes filters entirely. Use FILTER(ALL(...), condition) when you need a partial clear with logic. ST := DIVIDE([Units Sold], [Units Sold] + [Units OnHand]) Bridge contains unique pairs of the two dimensions. Set both relationships as single-direction or use bidirectional with care; or use USERELATIONSHIP.

Question 3 Power Bi Dax Common Patterns

What is the 'X-axis problem' in DAX measures?

L6M := CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -6, MONTH)) FYTD := CALCULATE([Total Sales], DATESYTD('Date'[Date], "06/30")) Auto-sum of a currency column may aggregate at wrong grain (line total vs order total). Convert to an explicit measure using SUMX over Quantity * Price. Measures changing label or granularity require SUMMARIZECOLUMNS or MIN/MAX over a column to make the visual consistent.

Question 4 Power Bi Dax Common Patterns

GROUPBY vs SUMMARIZE?

Use a parameter table disconnected from the model and SWITCH on SELECTEDVALUE; or a What-If parameter. Use a FactCurrencyRates table joined to Dates. Converted := SUMX(Sales, Sales[Amount] * LOOKUPVALUE(Rates[Rate], ...)) or convert via measure. PARALLELPERIOD always returns the full period at the level specified (e.g., full month even if filtered to mid-month). DATEADD shifts the existing period grain. GROUPBY requires an iterator (e.g., MAXX/GROUPBY(...)) to materialize values. SUMMARIZE is simpler for basic aggregations; SUMMARIZECOLUMNS is best for new visuals.

Question 5 Power Bi Dax Common Patterns

SUMMARIZE vs ADDCOLUMNS?

SPQ Sales := CALCULATE([Total Sales], DATEADD('Date'[Date], -1, QUARTER)) Use SUMX or a similar iterator over the relevant dimension to compute the correct total. Prefix with a folder-like convention (e.g., Sales | Total, Sales | YTD). Use spaces for display, no underscores needed. SUMMARIZE groups and aggregates; ADDCOLUMNS keeps all rows of the source. Prefer ADDCOLUMNS for measure evaluation across all rows.

Question 6 Power Bi Dax Common Patterns

Dynamic measure title text?

Trim, UPPER, replace synonyms. Or split into multiple columns. Materialize the cleaned version as a calculated column at refresh. Prefix with a folder-like convention (e.g., Sales | Total, Sales | YTD). Use spaces for display, no underscores needed. COUNTROWS: rows in a table. DISTINCTCOUNT: unique values of a column. COUNT: non-blank values of a column (counts duplicates). Title := "Sales: " & SELECTEDVALUE(Products[Category], "All") then reference in card title.

Question 7 Power Bi Dax Common Patterns

TRIM and CLEAN — use?

TRIM removes leading/trailing spaces and collapses internal. CLEAN removes non-printable chars. Useful when importing dirty text. Use GEOMEANX for compounded rates: GEOMEANX(VALUES('Date'[Month]), 1 + [MoM%]) - 1. Returns character count, not byte count. For UTF-8 multi-byte, results may surprise. Use LEN on the visible text. UNION(Sales_2024, Sales_2025) — stacks tables with same columns. Watch duplicates.

Question 8 Power Bi Dax Common Patterns

Dynamic segmentation — pattern?

Use a parameter table disconnected from the model and SWITCH on SELECTEDVALUE; or a What-If parameter. FYTD := CALCULATE([Total Sales], DATESYTD('Date'[Date], "06/30")) CALCULATE([Sales], FILTER(Sales, Sales[Margin] > 0)). Wrapping in CALCULATE(..., FILTER(...)) is correct. Column: computed row-by-row at refresh; stored in model; uses row context.
Measure: computed at query time; aggregates; uses filter context.

Question 9 Power Bi Dax Common Patterns

CROSSFILTER — when?

Customers with purchase in last 30 days but no purchase in 30–60 days window. Build via EXCEPT and CALCULATETABLE. Rank := RANKX(ALL(Products), [Total Sales],,DESC,Dense) Use in a measure to flip a relationship direction temporarily. Avoid: it often indicates a model design issue. SUBSTITUTE([Phone], "-", "") strips dashes. Supports a 4th instance number to replace only the Nth match.

Question 10 Power Bi Dax Common Patterns

Sales rank within category?

Each row's column values become filters on the model. Can give correct results but causes performance issues on large tables. Use nested IF/ISBLANK or BLANK() + x = x trick. DAX has no COALESCE; some tools expose it via extensions.

RT := CALCULATE([Total Sales], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]) && 'Date'[Year] = MAX('Date'[Year])))

RankInCat := RANKX(FILTER(ALL(Products), Products[Category] = MAX(Products[Category])), [Total Sales])

Question 11 Power Bi Dax Common Patterns

UNICHAR / UNICODE?

UNICHAR(8364) returns "€". UNICODE("A") = 65. Useful for symbols in dynamic titles. Apply a filter from a virtual table that has no physical relationship. CALCULATE([Sales], TREATAS(VALUES(SelCats), Products[Category])) IPT := DIVIDE(SUM(Sales[Qty]), DISTINCTCOUNT(Sales[OrderId])) SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) — evaluates per row, then sums.

Question 12 Power Bi Dax Common Patterns

Dynamic format string — example?

PLW Sales := CALCULATE([Total Sales], DATEADD('Date'[Date], -7, DAY)) Compute cumulative % of sales, assign A if <= 70%, B if <= 90%, C otherwise. Use VAR and RANKX. FORMAT_STRING = SWITCH(SELECTEDVALUE(Slicer[Unit]), "USD", "$#,##0", "EUR", "€#,##0", "#,##0") Title := "Sales: " & SELECTEDVALUE(Products[Category], "All") then reference in card title.

Question 13 Power Bi Dax Common Patterns

Conversion rate (visits → orders)?

CR := DIVIDE(DISTINCTCOUNT(Orders[Id]), DISTINCTCOUNT(Sessions[SessionId])) REMOVEFILTERS = CALCULATE(expr, ALL(...)) as a convenience. ALL returns a table usable in other functions. Tab in Power BI desktop to author and run DAX queries against the model directly. Useful for testing and learning. COUNTROWS reads a single column metadata flag. COUNT must evaluate each value's non-blank status.

Question 14 Power Bi Dax Common Patterns

Bidirectional vs single direction relationship?

Makes a filter additive/intersecting rather than replacing. CALCULATE([Sales], KEEPFILTERS(Products[Category] = "Bikes")) won't clobber an existing Category filter. Yes — prefer functions that the engine can push back into the storage engine: SUM, COUNTROWS, simple iterators over already-filtered tables. Single is safer and faster. Bidirectional needed only for many-to-many or specific filter propagation; can cause ambiguity. Trim, UPPER, replace synonyms. Or split into multiple columns. Materialize the cleaned version as a calculated column at refresh.

Question 15 Power Bi Dax Common Patterns

DISTINCT vs VALUES?

FORMAT_STRING = "$#,##0.00;-$#,##0.00;$0.00" — three sections: positive;negative;zero. FORMAT returns text and disables further numeric aggregation in visuals. Avoid inside measures that feed charts; use format strings on the model instead. PERCENTILEX.INC(FILTER(...), [Value], 0.9) for 90th percentile. Use .INC or .EXC per convention. DISTINCT returns unique rows of a table (removes duplicates and blank added row). VALUES returns distinct values but keeps BLANK for missing referential integrity.

Question 16 Power Bi Dax Common Patterns

UPPER / LOWER / PROPER?

DOH := DIVIDE([AvgInv], [DailyCOGS]) where DailyCOGS = DIVIDE([Total COGS], DISTINCTCOUNT('Date'[Date])). Standard case transforms. PROPER("john DOE") = "John Doe". ST := DIVIDE([Units Sold], [Units Sold] + [Units OnHand]) Title := "Sales: " & SELECTEDVALUE(Products[Category], "All") then reference in card title.

Question 17 Power Bi Dax Common Patterns

VAR inside iterator — why useful?

Materialize a value once per row to avoid recomputation: SUMX(Sales, VAR p = RELATED(Products[Price]) RETURN Sales[Qty] * p) SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) — evaluates per row, then sums. QTD Sales := CALCULATE([Total Sales], DATESQTD('Date'[Date])) FORMAT_STRING = SWITCH(SELECTEDVALUE(Slicer[Unit]), "USD", "$#,##0", "EUR", "€#,##0", "#,##0")

Question 18 Power Bi Dax Common Patterns

What does SAMEPERIODLASTYEAR actually return?

A table with the same number of dates shifted by one year, aligned to the grain of the current filter context. Dates not present in the prior year are skipped. YTD Sales := CALCULATE([Total Sales], DATESYTD('Date'[Date])) Bucket = IF([Sales] > 1000000, "Enterprise", IF([Sales] > 100000, "Mid", "SMB")) — works as a calculated column for slicers. Use RANKX and filter on rank ≤ 10.

Question 19 Power Bi Dax Common Patterns

What-If parameter — setup?

Total Sales := SUM(Sales[Amount]) CROSSJOIN: cartesian product. GENERATE: evaluates an expression per row of table1, often used to evaluate a measure per outer row. Modeling > New parameter > What-If. Creates a disconnected table and a generated measure. Use SELECTEDVALUE in your own measure to read the value. PLW Sales := CALCULATE([Total Sales], DATEADD('Date'[Date], -7, DAY))

Question 20 Power Bi Dax Common Patterns

What is USEROBJECTID()?

Returns the unique object ID of the current user (Azure AD). Useful for RLS against user tables that store object IDs. Use nested IF/ISBLANK or BLANK() + x = x trick. DAX has no COALESCE; some tools expose it via extensions. Use a FactCurrencyRates table joined to Dates. Converted := SUMX(Sales, Sales[Amount] * LOOKUPVALUE(Rates[Rate], ...)) or convert via measure. Checks if a column is at the level of detail of the current visual. Use to change behavior at totals vs rows.

Question 21 Power Bi Dax Common Patterns

GENERATE vs CROSSJOIN?

Bucket = IF([Sales] > 1000000, "Enterprise", IF([Sales] > 100000, "Mid", "SMB")) — works as a calculated column for slicers. CROSSJOIN: cartesian product. GENERATE: evaluates an expression per row of table1, often used to evaluate a measure per outer row. Inside a calculation item, returns the measure that was originally on the visual. Apply time intelligence to it generically. Rank := RANKX(ALL(Products), [Total Sales],,DESC,Dense)

Question 22 Power Bi Dax Common Patterns

Iterator function example?

DIVIDE handles divide-by-zero gracefully (returns BLANK or 3rd arg). Slash returns infinity error. SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) — evaluates per row, then sums. MEDIANX evaluates an expression per row of an iterator. MEDIANX(Sales, Sales[Amount]) = median of Amount. .P: population std dev (n in denominator). .S: sample (n-1). Match to Excel's STDEV.P/STDEV.S.

Question 23 Power Bi Dax Common Patterns

DAX 'context transition' performance tip?

Modifies filter context. Replaces or adds filters before evaluating its expression. Avoid wrapping iterators inside CALCULATE unless needed; can cause expensive row→filter transitions on large tables. GROUPBY requires an iterator (e.g., MAXX/GROUPBY(...)) to materialize values. SUMMARIZE is simpler for basic aggregations; SUMMARIZECOLUMNS is best for new visuals. Stockout := CALCULATE(DISTINCTCOUNT('Date'[Date]), FILTER(Inventory, Inventory[OnHand] = 0))

Question 24 Power Bi Dax Common Patterns

Avoid CALCULATE in calculated columns?

CR := DIVIDE(DISTINCTCOUNT(Orders[Id]), DISTINCTCOUNT(Sessions[SessionId])) UNION(Sales_2024, Sales_2025) — stacks tables with same columns. Watch duplicates. Yes, when possible. Pre-compute via RELATED or direct column expressions. CALCULATE in a column forces materialization of filters and is row-by-row slow. Yes — DISTINCTCOUNT on free-text is expensive. Use a surrogate integer key for cardinality.

Question 25 Power Bi Dax Common Patterns

EXCEPT — use case?

Rows in first table not in second. EXCEPT(AllCustomers, ChurnedCustomers) = active customers. IPT := DIVIDE(SUM(Sales[Qty]), DISTINCTCOUNT(Sales[OrderId])) Total Sales := SUM(Sales[Amount]) ADDCOLUMNS(Products, "Sales", [Total Sales]) — adds a calculated column at query time without bloating the model.

Question 26 Power Bi Dax Common Patterns

Top 10 products dynamically in a measure?

NewCust := CALCULATE(DISTINCTCOUNT(Sales[CustomerId]), FILTER(ALL(Customers), Customers[FirstPurchaseDate] >= MIN('Date'[Date]) && Customers[FirstPurchaseDate] <= MAX('Date'[Date])))

Use RANKX and filter on rank ≤ 10. EVALUATE TOPN(5, 'Product', [Total Sales], DESC) — returns top 5 products by measure. Each row's column values become filters on the model. Can give correct results but causes performance issues on large tables.

Question 27 Power Bi Dax Common Patterns

PARALLELPERIOD vs DATEADD?

Measures are evaluated separately at each row + total. The total is computed once for the whole context, not summed from rows. Measures changing label or granularity require SUMMARIZECOLUMNS or MIN/MAX over a column to make the visual consistent. Create a cohort index column at first purchase month. Then retention = customers active in cohort month N / cohort size. Use SUMMARIZECOLUMNS. PARALLELPERIOD always returns the full period at the level specified (e.g., full month even if filtered to mid-month). DATEADD shifts the existing period grain.

Question 28 Power Bi Dax Common Patterns

Same period last week?

Use a FactCurrencyRates table joined to Dates. Converted := SUMX(Sales, Sales[Amount] * LOOKUPVALUE(Rates[Rate], ...)) or convert via measure. USERPRINCIPALNAME(): UPN (email-style). USERNAME(): domain\user in some contexts; not always populated. Prefer USERPRINCIPALNAME() in cloud. Measures are evaluated separately at each row + total. The total is computed once for the whole context, not summed from rows. PLW Sales := CALCULATE([Total Sales], DATEADD('Date'[Date], -7, DAY))

Question 29 Power Bi Dax Common Patterns

SWITCH for measure branching?

Use GEOMEANX for compounded rates: GEOMEANX(VALUES('Date'[Month]), 1 + [MoM%]) - 1. Check if a column has a single value in current context. Useful for conditional measures shown in totals. Measure := SWITCH(SELECTEDVALUE(Slicer[KPI]), "Sales", [Total Sales], "Margin", [Total Margin], BLANK()) Yes — DISTINCTCOUNT on free-text is expensive. Use a surrogate integer key for cardinality.

Question 30 Power Bi Dax Common Patterns

Many-to-many with bridge table — pattern?

Bridge contains unique pairs of the two dimensions. Set both relationships as single-direction or use bidirectional with care; or use USERELATIONSHIP. Standard case transforms. PROPER("john DOE") = "John Doe". Use GEOMEANX for compounded rates: GEOMEANX(VALUES('Date'[Month]), 1 + [MoM%]) - 1. Returns the unique object ID of the current user (Azure AD). Useful for RLS against user tables that store object IDs.

Question 31 Power Bi Dax Common Patterns

Trailing 12 months total?

TTM := CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)) Bridge contains unique pairs of the two dimensions. Set both relationships as single-direction or use bidirectional with care; or use USERELATIONSHIP. PY Sales := CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

RT := CALCULATE([Total Sales], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]) && 'Date'[Year] = MAX('Date'[Year])))

Question 32 Power Bi Dax Common Patterns

DAX query view (DQV) — what is it?

Measure := SWITCH(SELECTEDVALUE(Slicer[KPI]), "Sales", [Total Sales], "Margin", [Total Margin], BLANK()) PERCENTILEX.INC(FILTER(...), [Value], 0.9) for 90th percentile. Use .INC or .EXC per convention. Tab in Power BI desktop to author and run DAX queries against the model directly. Useful for testing and learning. Checks if a column is at the level of detail of the current visual. Use to change behavior at totals vs rows.

Question 33 Power Bi Dax Common Patterns

Boolean filter: 'Sales where Margin > 0'?

Bridge contains unique pairs of the two dimensions. Set both relationships as single-direction or use bidirectional with care; or use USERELATIONSHIP. FORMAT_STRING = SWITCH(SELECTEDVALUE(Slicer[Unit]), "USD", "$#,##0", "EUR", "€#,##0", "#,##0") CALCULATE([Sales], FILTER(Sales, Sales[Margin] > 0)). Wrapping in CALCULATE(..., FILTER(...)) is correct. Calculated columns grow the model size; measures don't. Prefer measures unless you need the value as a slicer/filter.

Question 34 Power Bi Dax Common Patterns

Reactivated customers?

FYTD := CALCULATE([Total Sales], DATESYTD('Date'[Date], "06/30")) ADS := DIVIDE([Total Sales], DISTINCTCOUNT('Date'[Date])) Power BI auto-creates aggregations for numeric columns (SUM by default). Explicit measures give consistent behavior, format strings, and avoid double counting. Customers with purchase in last 30 days but no purchase in 30–60 days window. Build via EXCEPT and CALCULATETABLE.

Question 35 Power Bi Dax Common Patterns

Year-to-date sales?

SELECTEDVALUE(Products[Category], "All Categories") — second arg is returned when no single value is selected. DIVIDE handles divide-by-zero gracefully (returns BLANK or 3rd arg). Slash returns infinity error. Yes — DISTINCTCOUNT on free-text is expensive. Use a surrogate integer key for cardinality. YTD Sales := CALCULATE([Total Sales], DATESYTD('Date'[Date]))

Question 36 Power Bi Dax Common Patterns

Pattern: 'sales for top 5 customers only'?

A row inside a Calculation Group defining an applied transformation (e.g., "YTD", "PY"). One CG replaces dozens of branched measures. FORMAT_STRING = SWITCH(SELECTEDVALUE(Slicer[Unit]), "USD", "$#,##0", "EUR", "€#,##0", "#,##0") Check if a column has a single value in current context. Useful for conditional measures shown in totals. CALCULATE([Total Sales], TOPN(5, VALUES(Customers[Id]), [Total Sales], DESC))

Question 37 Power Bi Dax Common Patterns

Common iterator functions?

Reads the format string of the current measure. SELECTEDMEASURE() alone returns its numeric value. SUMX, AVERAGEX, COUNTX, MINX, MAXX, RANKX, FILTER, ADDCOLUMNS. Returns table of dates between two boundaries. Use inside CALCULATE for custom windows. TTM := CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH))

Question 38 Power Bi Dax Common Patterns

Days inventory on hand?

EVALUATE ROW("X", [Total Sales], "Y", [Customers]) — returns one row with named scalar columns. DOH := DIVIDE([AvgInv], [DailyCOGS]) where DailyCOGS = DIVIDE([Total COGS], DISTINCTCOUNT('Date'[Date])). Stockout := CALCULATE(DISTINCTCOUNT('Date'[Date]), FILTER(Inventory, Inventory[OnHand] = 0)) ALLEXCEPT(Products, Products[Category]) — clears all filters on Products except Category.

Question 39 Power Bi Dax Common Patterns

Same period last quarter?

Use a FactCurrencyRates table joined to Dates. Converted := SUMX(Sales, Sales[Amount] * LOOKUPVALUE(Rates[Rate], ...)) or convert via measure. SPQ Sales := CALCULATE([Total Sales], DATEADD('Date'[Date], -1, QUARTER)) A measure reference without CALCULATE wrapper, e.g., [Total Sales] in a calculated column. Triggers context transition — usually a bug. BLANK() is absence of value (treated as 0 in numerics, ignored in aggregations). Use it as the default in DIVIDE for clean visuals.

Question 40 Power Bi Dax Common Patterns

COMBINEVALUES vs CONCATENATE?

MTD Sales := CALCULATE([Total Sales], DATESMTD('Date'[Date])) QTD Sales := CALCULATE([Total Sales], DATESQTD('Date'[Date])) COMBINEVALUES(",", [City], [Country]) handles BLANKs gracefully (skips them). CONCATENATE returns BLANK if any arg is blank. Customers with purchase in last 30 days but no purchase in 30–60 days window. Build via EXCEPT and CALCULATETABLE.

Question 41 Power Bi Dax Common Patterns

Differences between CALCULATE and CALCULATETABLE?

FORMAT_STRING = "0.0%" in measure properties; or use FORMAT() function inline. Power BI auto-creates aggregations for numeric columns (SUM by default). Explicit measures give consistent behavior, format strings, and avoid double counting. CALCULATE returns a scalar, CALCULATETABLE returns a table. Both modify filter context. SUMMARIZE groups and aggregates; ADDCOLUMNS keeps all rows of the source. Prefer ADDCOLUMNS for measure evaluation across all rows.

Question 42 Power Bi Dax Common Patterns

SELECTEDVALUE?

RT := CALCULATE([Total Sales], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]) && 'Date'[Year] = MAX('Date'[Year])))

Mark a table as Date Table (Modeling tab) with continuous, unique dates and no missing values. Returns the selected value if there's exactly one; otherwise returns blank (or alt). Replaces VALUES(...) + IF(HASONEVALUE). CALCULATE([Sales], Products[Price] > 100) — the filter overrides existing Price filters. Use FILTER for context-aware comparisons.

Question 43 Power Bi Dax Common Patterns

LOOKUPVALUE inside calculated column — caveat?

Check if a column has a single value in current context. Useful for conditional measures shown in totals. FORMAT returns text and disables further numeric aggregation in visuals. Avoid inside measures that feed charts; use format strings on the model instead. EVALUATE ROW("X", [Total Sales], "Y", [Customers]) — returns one row with named scalar columns. Performs an arbitrary lookup, ignores model relationships, and is row-by-row slow. Prefer RELATED when a relationship exists.

Question 44 Power Bi Dax Common Patterns

Filter to specific category?

Returns character count, not byte count. For UTF-8 multi-byte, results may surprise. Use LEN on the visible text. Bike Sales := CALCULATE([Total Sales], Products[Category] = "Bikes") Modeling > New parameter > What-If. Creates a disconnected table and a generated measure. Use SELECTEDVALUE in your own measure to read the value. ADDCOLUMNS(Products, "Sales", [Total Sales]) — adds a calculated column at query time without bloating the model.

Question 45 Power Bi Dax Common Patterns

Description field on measures?

Use RANKX and filter on rank ≤ 10. When a virtual table expression (e.g., FILTER) is forced to be evaluated row-by-row into a physical result. Causes memory and CPU cost. Document the measure's intent, grain, and edge cases. Tooltip in field list helps report authors. A measure reference without CALCULATE wrapper, e.g., [Total Sales] in a calculated column. Triggers context transition — usually a bug.

Question 46 Power Bi Dax Common Patterns

Stockout days — pattern?

ADDCOLUMNS(Products, "Sales", [Total Sales]) — adds a calculated column at query time without bloating the model. Stockout := CALCULATE(DISTINCTCOUNT('Date'[Date]), FILTER(Inventory, Inventory[OnHand] = 0)) Bucket = IF([Sales] > 1000000, "Enterprise", IF([Sales] > 100000, "Mid", "SMB")) — works as a calculated column for slicers. vsBudget := DIVIDE([Actual] - [Budget], [Budget]). For dynamic budget selection, use SWITCH on a slicer.

Question 47 Power Bi Dax Common Patterns

Basic measure: total sales?

Total Sales := SUM(Sales[Amount]) Returns table of dates between two boundaries. Use inside CALCULATE for custom windows. A row inside a Calculation Group defining an applied transformation (e.g., "YTD", "PY"). One CG replaces dozens of branched measures. HASONEFILTER: a single direct filter exists. HASONEVALUE: the column resolves to one value in current context. Use HASONEVALUE in measures.

Question 48 Power Bi Dax Common Patterns

COUNTROWS vs DISTINCTCOUNT vs COUNT?

COUNTROWS: rows in a table. DISTINCTCOUNT: unique values of a column. COUNT: non-blank values of a column (counts duplicates). CALCULATE([Sales], Products[Price] > 100) — the filter overrides existing Price filters. Use FILTER for context-aware comparisons. R7 := AVERAGEX(DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -7, DAY), [Total Sales]) Returns character count, not byte count. For UTF-8 multi-byte, results may surprise. Use LEN on the visible text.

Question 49 Power Bi Dax Common Patterns

EVALUATE ROW construct?

PM Sales := CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH)) GENERATESERIES(0, 100, 10) — returns a single-column table of numbers 0,10,20,...,100. Useful for dynamic axes (e.g., NPS buckets). DOH := DIVIDE([AvgInv], [DailyCOGS]) where DailyCOGS = DIVIDE([Total COGS], DISTINCTCOUNT('Date'[Date])). EVALUATE ROW("X", [Total Sales], "Y", [Customers]) — returns one row with named scalar columns.

Question 50 Power Bi Dax Common Patterns

Total YTD vs Total QTD conflict in a visual?

Stockout := CALCULATE(DISTINCTCOUNT('Date'[Date]), FILTER(Inventory, Inventory[OnHand] = 0)) CALCULATE([Sales], Products[Price] > 100) — the filter overrides existing Price filters. Use FILTER for context-aware comparisons. Numeric keys may be interpreted as measures and force aggregation. Mark as dimension / do not summarize. Only one time intelligence filter can apply at once. Use calculation groups or separate measures to avoid filters stacking.