Title: Power Bi Dax Common Patterns — LearnCoachAssist

Q: Difference between calculated column and measure?
A: Column: computed row-by-row at refresh; stored in model; uses row context.
Measure: computed at query time; aggregates; uses filter context.

Q: Basic measure: total sales?
A: Total Sales := SUM(Sales[Amount])

Q: Distinct customer count?
A: Customers := DISTINCTCOUNT(Sales[CustomerId])

Q: Sales for prior year (same period)?
A: PY Sales := CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

Q: YoY % change?
A: YoY % := DIVIDE([Total Sales] - [PY Sales], [PY Sales])

Q: Year-to-date sales?
A: YTD Sales := CALCULATE([Total Sales], DATESYTD('Date'[Date]))

Q: Filter to specific category?
A: Bike Sales := CALCULATE([Total Sales], Products[Category] = "Bikes")

Q: Why use DIVIDE vs / ?
A: DIVIDE handles divide-by-zero gracefully (returns BLANK or 3rd arg). Slash returns infinity error.

Q: CALCULATE — main effect?
A: Modifies filter context. Replaces or adds filters before evaluating its expression.

Q: Remove all filters from a column?
A: CALCULATE([Total Sales], ALL(Products[Category]))

Q: Keep only existing filters from a single column?
A: ALLEXCEPT(Products, Products[Category]) — clears all filters on Products except Category.

Q: % of total in a visual?
A: %Total := DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED()))

Q: Difference: ALL vs ALLSELECTED?
A: ALL: ignores ALL filters (including visual slicers).
ALLSELECTED: respects user-applied slicers; ignores row context within a visual.

Q: Iterator function example?
A: SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) — evaluates per row, then sums.

Q: Common iterator functions?
A: SUMX, AVERAGEX, COUNTX, MINX, MAXX, RANKX, FILTER, ADDCOLUMNS.

Q: Row context vs filter context?
A: Row context: 'current row' (created by iterators or in columns).
Filter context: set of filters applied (from visuals, slicers, CALCULATE).

Q: CALCULATE creates row → filter context transition — meaning?
A: Inside a calculated column or iterator, the current row's values become filters on the table. Surprises beginners but is powerful.

Q: Cumulative sales?
A: Cumulative := CALCULATE([Total Sales], FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date])))

Q: Top 10 products dynamically in a measure?
A: Use RANKX and filter on rank ≤ 10.

Q: RANKX example?
A: Rank := RANKX(ALL(Products), [Total Sales],,DESC,Dense)

Q: VAR / RETURN benefit?
A: Reusable expression, only evaluated once, more readable, can return tables or scalars.

Q: Time intelligence prerequisite?
A: Mark a table as Date Table (Modeling tab) with continuous, unique dates and no missing values.

Q: DATESBETWEEN?
A: Returns table of dates between two boundaries. Use inside CALCULATE for custom windows.

Q: Rolling 7-day average?
A: R7 := AVERAGEX(DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -7, DAY), [Total Sales])

Q: USERELATIONSHIP — when?
A: Activate an inactive relationship for a specific measure (e.g., calculating by ship date instead of order date).

Q: Star schema — why preferred?
A: Fewer joins, simpler measures, faster engine. Replace snowflake by flattening dimensions.

Q: Bidirectional vs single direction relationship?
A: Single is safer and faster. Bidirectional needed only for many-to-many or specific filter propagation; can cause ambiguity.

Q: Common DAX mistake: filtering with column > static value not respecting context?
A: CALCULATE([Sales], Products[Price] > 100) — the filter overrides existing Price filters. Use FILTER for context-aware comparisons.

Q: HASONEVALUE use case?
A: Check if a column has a single value in current context. Useful for conditional measures shown in totals.

Q: SELECTEDVALUE?
A: Returns the selected value if there's exactly one; otherwise returns blank (or alt). Replaces VALUES(...) + IF(HASONEVALUE).

Q: Why measure totals don't add up?
A: Measures are evaluated separately at each row + total. The total is computed once for the whole context, not summed from rows.

Q: Fix total row when measure is a ratio?
A: Use SUMX or a similar iterator over the relevant dimension to compute the correct total.

Q: Calculation Groups?
A: Reusable calculation patterns applied to many measures (e.g., one CG defines YTD, PY, MTD for all measures). Tabular Editor needed.

Q: Format string for percentages?
A: FORMAT_STRING = "0.0%" in measure properties; or use FORMAT() function inline.

Q: DAX 'context transition' performance tip?
A: Avoid wrapping iterators inside CALCULATE unless needed; can cause expensive row→filter transitions on large tables.

Q: Sum products as 'revenue'?
A: Revenue := SUMX(Sales, Sales[Qty] * RELATED(Products[Price])) if price lives in Products table.

Q: RELATED vs RELATEDTABLE?
A: RELATED: scalar from many-to-one direction (child to parent).
RELATEDTABLE: table from one-to-many.

Q: Common pitfall: using Calculated Columns where Measures suffice?
A: Calculated columns grow the model size; measures don't. Prefer measures unless you need the value as a slicer/filter.

Q: Same period last quarter?
A: SPQ Sales := CALCULATE([Total Sales], DATEADD('Date'[Date], -1, QUARTER))

Q: Previous month value?
A: PM Sales := CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH))

Q: Same period last week?
A: PLW Sales := CALCULATE([Total Sales], DATEADD('Date'[Date], -7, DAY))

Q: Quarter-to-date sales?
A: QTD Sales := CALCULATE([Total Sales], DATESQTD('Date'[Date]))

Q: Month-to-date sales?
A: MTD Sales := CALCULATE([Total Sales], DATESMTD('Date'[Date]))

Q: Last 30 days (rolling window)?
A: L30 := CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -30, DAY))

Q: Last 6 months rolling?
A: L6M := CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -6, MONTH))

Q: Trailing 12 months total?
A: TTM := CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH))

Q: Fiscal year-to-date (e.g., FY ending June)?
A: FYTD := CALCULATE([Total Sales], DATESYTD('Date'[Date], "06/30"))

Q: What does SAMEPERIODLASTYEAR actually return?
A: 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.

Q: PARALLELPERIOD vs DATEADD?
A: 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.

Q: Total YTD vs Total QTD conflict in a visual?
A: Only one time intelligence filter can apply at once. Use calculation groups or separate measures to avoid filters stacking.

Q: Running total that respects year boundary?
A: RT := CALCULATE([Total Sales], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]) && 'Date'[Year] = MAX('Date'[Year])))

Q: Average daily sales (context-aware)?
A: ADS := DIVIDE([Total Sales], DISTINCTCOUNT('Date'[Date]))

Q: What does BLANK() mean vs 0?
A: BLANK() is absence of value (treated as 0 in numerics, ignored in aggregations). Use it as the default in DIVIDE for clean visuals.

Q: COALESCE alternative in DAX?
A: Use nested IF/ISBLANK or BLANK() + x = x trick. DAX has no COALESCE; some tools expose it via extensions.

Q: IF vs SWITCH for branched measures?
A: SWITCH(TRUE(), ...) is cleaner for many branches based on conditions. Use simple IF for binary logic.

Q: Dynamic measure title text?
A: Title := "Sales: " & SELECTEDVALUE(Products[Category], "All") then reference in card title.

Q: CONCATENATEX — when?
A: Aggregate a text column across rows, e.g., list of top products. CONCATENATEX(TOPN(3, Products, [Sales]), Products[Name], ", ")

Q: UNION of two tables?
A: UNION(Sales_2024, Sales_2025) — stacks tables with same columns. Watch duplicates.

Q: DISTINCT vs VALUES?
A: DISTINCT returns unique rows of a table (removes duplicates and blank added row). VALUES returns distinct values but keeps BLANK for missing referential integrity.

Q: EXCEPT — use case?
A: Rows in first table not in second. EXCEPT(AllCustomers, ChurnedCustomers) = active customers.

Q: INTERSECT use case?
A: Customers who bought in BOTH 2024 and 2025. INTERSECT(Cust2024, Cust2025)

Q: GENERATESERIES — example?
A: GENERATESERIES(0, 100, 10) — returns a single-column table of numbers 0,10,20,...,100. Useful for dynamic axes (e.g., NPS buckets).

Q: GENERATE vs CROSSJOIN?
A: CROSSJOIN: cartesian product. GENERATE: evaluates an expression per row of table1, often used to evaluate a measure per outer row.

Q: ADDCOLUMNS example?
A: ADDCOLUMNS(Products, "Sales", [Total Sales]) — adds a calculated column at query time without bloating the model.

Q: SUMMARIZE vs ADDCOLUMNS?
A: SUMMARIZE groups and aggregates; ADDCOLUMNS keeps all rows of the source. Prefer ADDCOLUMNS for measure evaluation across all rows.

Q: SUMMARIZECOLUMNS — why preferred over SUMMARIZE in measures?
A: SUMMARIZECOLUMNS is engine-optimized for visuals and respects filter context automatically. Use it inside calculated tables or when building virtual tables.

Q: GROUPBY vs SUMMARIZE?
A: GROUPBY requires an iterator (e.g., MAXX/GROUPBY(...)) to materialize values. SUMMARIZE is simpler for basic aggregations; SUMMARIZECOLUMNS is best for new visuals.

Q: TREATAS — when?
A: Apply a filter from a virtual table that has no physical relationship. CALCULATE([Sales], TREATAS(VALUES(SelCats), Products[Category]))

Q: TREATAS vs INTERSECT?
A: TREATAS remaps columns of an in-memory table to a real model column. INTERSECT returns rows in common; doesn't add filters by itself.

Q: LOOKUPVALUE — example?
A: LOOKUPVALUE(Products[Price], Products[Id], Sales[ProductId]) — returns scalar; BLANK if not found. Avoid in row-by-row hot paths; use RELATED.

Q: LOOKUPVALUE inside calculated column — caveat?
A: Performs an arbitrary lookup, ignores model relationships, and is row-by-row slow. Prefer RELATED when a relationship exists.

Q: Differences between CALCULATE and CALCULATETABLE?
A: CALCULATE returns a scalar, CALCULATETABLE returns a table. Both modify filter context.

Q: KEEPFILTERS — what does it do?
A: Makes a filter additive/intersecting rather than replacing. CALCULATE([Sales], KEEPFILTERS(Products[Category] = "Bikes")) won't clobber an existing Category filter.

Q: REMOVEFILTERS vs ALL?
A: REMOVEFILTERS = CALCULATE(expr, ALL(...)) as a convenience. ALL returns a table usable in other functions.

Q: When to use ALL vs FILTER(ALL(...), ...)?
A: ALL alone removes filters entirely. Use FILTER(ALL(...), condition) when you need a partial clear with logic.

Q: CROSSFILTER — when?
A: Use in a measure to flip a relationship direction temporarily. Avoid: it often indicates a model design issue.

Q: Many-to-many with bridge table — pattern?
A: Bridge contains unique pairs of the two dimensions. Set both relationships as single-direction or use bidirectional with care; or use USERELATIONSHIP.

Q: Row-level security (RLS) — basic pattern?
A: In Modeling > Manage Roles, filter a dimension table: [Email] = USERPRINCIPALNAME(). Tested via "View as Role".

Q: Dynamic RLS pattern?
A: [Region] IN VALUES(Users[Region]) where a mapping table Users[Email, Region] is joined to employees. RLS is applied through the relationship.

Q: What is USEROBJECTID()?
A: Returns the unique object ID of the current user (Azure AD). Useful for RLS against user tables that store object IDs.

Q: DAX query view (DQV) — what is it?
A: Tab in Power BI desktop to author and run DAX queries against the model directly. Useful for testing and learning.

Q: DEFINE MEASURE in DAX queries?
A: DEFINE MEASURE Sales[Total] = SUM(Sales[Amount]) EVALUATE ... — allows measure reuse inside a query without altering the model.

Q: EVALUATE ROW construct?
A: EVALUATE ROW("X", [Total Sales], "Y", [Customers]) — returns one row with named scalar columns.

Q: EVALUATE TOPN — example?
A: EVALUATE TOPN(5, 'Product', [Total Sales], DESC) — returns top 5 products by measure.

Q: ORDER BY in DAX queries?
A: EVALUATE ... ORDER BY [Sales] DESC. Works on the result of the query, not within SUMMARIZECOLUMNS.

Q: What is a calculation item?
A: A row inside a Calculation Group defining an applied transformation (e.g., "YTD", "PY"). One CG replaces dozens of branched measures.

Q: SELECTEDMEASURE — when?
A: Inside a calculation item, returns the measure that was originally on the visual. Apply time intelligence to it generically.

Q: SELECTEDMEASUREFORMATSTRING?
A: Reads the format string of the current measure. SELECTEDMEASURE() alone returns its numeric value.

Q: ISINSCOPE — when?
A: Checks if a column is at the level of detail of the current visual. Use to change behavior at totals vs rows.

Q: ISFILTERED vs ISCROSSFILTERED?
A: ISFILTERED: column is directly filtered. ISCROSSFILTERED: column is filtered through relationships or other columns. Use ISFILTERED for RLS/visuals checks.

Q: HASONEFILTER vs HASONEVALUE?
A: HASONEFILTER: a single direct filter exists. HASONEVALUE: the column resolves to one value in current context. Use HASONEVALUE in measures.

Q: COUNTROWS vs DISTINCTCOUNT vs COUNT?
A: COUNTROWS: rows in a table. DISTINCTCOUNT: unique values of a column. COUNT: non-blank values of a column (counts duplicates).

Q: COUNTBLANK — use case?
A: Count missing values in a column. Often used as a data quality measure: COUNTBLANK(Customers[Email]).

Q: ISBLANK vs =BLANK()?
A: ISBLANK(x) tests for blank. x = BLANK() also works because BLANK coerces to 0 in numerics — be careful with numeric comparisons.

Q: Avoid implicit measures — why?
A: Power BI auto-creates aggregations for numeric columns (SUM by default). Explicit measures give consistent behavior, format strings, and avoid double counting.

Q: Implicit measure pitfalls with currency?
A: 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.

Q: Best practice: naming measures?
A: Prefix with a folder-like convention (e.g., Sales | Total, Sales | YTD). Use spaces for display, no underscores needed.

Q: Display folder — purpose?
A: Organize measures in the field list. DISPLAYFOLDER = "Sales\Time Intel" in model metadata.

Q: Description field on measures?
A: Document the measure's intent, grain, and edge cases. Tooltip in field list helps report authors.

Q: FORMAT function performance?
A: FORMAT returns text and disables further numeric aggregation in visuals. Avoid inside measures that feed charts; use format strings on the model instead.

Q: USERNAME() vs USERPRINCIPALNAME()?
A: USERPRINCIPALNAME(): UPN (email-style). USERNAME(): domain\user in some contexts; not always populated. Prefer USERPRINCIPALNAME() in cloud.

Q: CUSTOMDATA() — when?
A: Reads a value passed from the embedding app via the CustomData property. Used in OEM/embedded RLS scenarios.

Q: CONTAINS function — purpose?
A: Row-by-row check if a table contains a row matching a condition. Slower than LOOKUPVALUE; usually COUNTROWS(FILTER(...))>0 is faster.

Q: CONTAINSSTRING vs SEARCH?
A: CONTAINSSTRING: case-insensitive substring search. SEARCH: case-insensitive with wildcards (*, ?). Both return TRUE/FALSE.

Q: FIND vs SEARCH?
A: FIND is case-sensitive, no wildcards, returns position. SEARCH is case-insensitive, supports wildcards. Use FIND for precise matching.

Q: LEFT, RIGHT, MID basics?
A: Extract substrings. MID([Text], 2, 3) returns 3 characters starting at position 2. Combine with FIND for parsing.

Q: TRIM and CLEAN — use?
A: TRIM removes leading/trailing spaces and collapses internal. CLEAN removes non-printable chars. Useful when importing dirty text.

Q: UPPER / LOWER / PROPER?
A: Standard case transforms. PROPER("john DOE") = "John Doe".

Q: SUBSTITUTE — example?
A: SUBSTITUTE([Phone], "-", "") strips dashes. Supports a 4th instance number to replace only the Nth match.

Q: LEN — common pitfall?
A: Returns character count, not byte count. For UTF-8 multi-byte, results may surprise. Use LEN on the visible text.

Q: UNICHAR / UNICODE?
A: UNICHAR(8364) returns "€". UNICODE("A") = 65. Useful for symbols in dynamic titles.

Q: COMBINEVALUES vs CONCATENATE?
A: COMBINEVALUES(",", [City], [Country]) handles BLANKs gracefully (skips them). CONCATENATE returns BLANK if any arg is blank.

Q: SELECTEDVALUE with alternate default?
A: SELECTEDVALUE(Products[Category], "All Categories") — second arg is returned when no single value is selected.

Q: Sales rank within category?
A: RankInCat := RANKX(FILTER(ALL(Products), Products[Category] = MAX(Products[Category])), [Total Sales])

Q: Percentile rank?
A: PERCENTILEX.INC(FILTER(...), [Value], 0.9) for 90th percentile. Use .INC or .EXC per convention.

Q: MEDIANX vs MEDIAN?
A: MEDIANX evaluates an expression per row of an iterator. MEDIANX(Sales, Sales[Amount]) = median of Amount.

Q: STDEVX.P vs STDEVX.S?
A: .P: population std dev (n in denominator). .S: sample (n-1). Match to Excel's STDEV.P/STDEV.S.

Q: VAR inside iterator — why useful?
A: Materialize a value once per row to avoid recomputation: SUMX(Sales, VAR p = RELATED(Products[Price]) RETURN Sales[Qty] * p)

Q: What is a 'naked' measure?
A: A measure reference without CALCULATE wrapper, e.g., [Total Sales] in a calculated column. Triggers context transition — usually a bug.

Q: Context transition inside calculated column?
A: Each row's column values become filters on the model. Can give correct results but causes performance issues on large tables.

Q: Avoid CALCULATE in calculated columns?
A: 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.

Q: Boolean filter: 'Sales where Margin > 0'?
A: CALCULATE([Sales], FILTER(Sales, Sales[Margin] > 0)). Wrapping in CALCULATE(..., FILTER(...)) is correct.

Q: Pattern: 'sales for top 5 customers only'?
A: CALCULATE([Total Sales], TOPN(5, VALUES(Customers[Id]), [Total Sales], DESC))

Q: Pattern: 'new customers this month'?
A: NewCust := CALCULATE(DISTINCTCOUNT(Sales[CustomerId]), FILTER(ALL(Customers), Customers[FirstPurchaseDate] >= MIN('Date'[Date]) && Customers[FirstPurchaseDate] <= MAX('Date'[Date])))

Q: Pattern: 'churned customers'?
A: Customers with no purchases in last 90 days: VAR Last90 = DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -90, DAY) RETURN CALCULATE(DISTINCTCOUNT(Customers[Id]), EXCEPT(VALUES(Customers[Id]), CALCULATETABLE(VALUES(Customers[Id]), Sales, Last90)))

Q: Static segmentation bucket — pattern?
A: Bucket = IF([Sales] > 1000000, "Enterprise", IF([Sales] > 100000, "Mid", "SMB")) — works as a calculated column for slicers.

Q: Dynamic segmentation — pattern?
A: Use a parameter table disconnected from the model and SWITCH on SELECTEDVALUE; or a What-If parameter.

Q: What-If parameter — setup?
A: Modeling > New parameter > What-If. Creates a disconnected table and a generated measure. Use SELECTEDVALUE in your own measure to read the value.

Q: Dynamic ABC classification?
A: Compute cumulative % of sales, assign A if <= 70%, B if <= 90%, C otherwise. Use VAR and RANKX.

Q: Stockout days — pattern?
A: Stockout := CALCULATE(DISTINCTCOUNT('Date'[Date]), FILTER(Inventory, Inventory[OnHand] = 0))

Q: Days inventory on hand?
A: DOH := DIVIDE([AvgInv], [DailyCOGS]) where DailyCOGS = DIVIDE([Total COGS], DISTINCTCOUNT('Date'[Date])).

Q: Sell-through rate?
A: ST := DIVIDE([Units Sold], [Units Sold] + [Units OnHand])

Q: Conversion rate (visits → orders)?
A: CR := DIVIDE(DISTINCTCOUNT(Orders[Id]), DISTINCTCOUNT(Sessions[SessionId]))

Q: Average order value (AOV)?
A: AOV := DIVIDE([Total Sales], DISTINCTCOUNT(Sales[OrderId]))

Q: Items per transaction?
A: IPT := DIVIDE(SUM(Sales[Qty]), DISTINCTCOUNT(Sales[OrderId]))

Q: Active customers (90 days)?
A: Active90 := CALCULATE(DISTINCTCOUNT(Sales[CustomerId]), DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -90, DAY))

Q: Reactivated customers?
A: Customers with purchase in last 30 days but no purchase in 30–60 days window. Build via EXCEPT and CALCULATETABLE.

Q: Cohort retention — DAX pattern?
A: Create a cohort index column at first purchase month. Then retention = customers active in cohort month N / cohort size. Use SUMMARIZECOLUMNS.

Q: Pareto 80/20 — DAX pattern?
A: Cum% := DIVIDE(CALCULATE([Total Sales], FILTER(ALL(Products), [Rank]<=EARLIER([Rank]))), [Total Sales]) in a calculated column. Mark products where Cum%<=80%.

Q: Weighted average — pattern?
A: WeightedPrice := SUMX(Sales, Sales[Qty] * Sales[UnitPrice]) / SUM(Sales[Qty])

Q: Geomean vs AVERAGE for growth rates?
A: Use GEOMEANX for compounded rates: GEOMEANX(VALUES('Date'[Month]), 1 + [MoM%]) - 1.

Q: Pattern: comparison vs budget?
A: vsBudget := DIVIDE([Actual] - [Budget], [Budget]). For dynamic budget selection, use SWITCH on a slicer.

Q: SWITCH for measure branching?
A: Measure := SWITCH(SELECTEDVALUE(Slicer[KPI]), "Sales", [Total Sales], "Margin", [Total Margin], BLANK())

Q: Dynamic currency conversion?
A: Use a FactCurrencyRates table joined to Dates. Converted := SUMX(Sales, Sales[Amount] * LOOKUPVALUE(Rates[Rate], ...)) or convert via measure.

Q: Currency format string?
A: FORMAT_STRING = "$#,##0.00;-$#,##0.00;$0.00" — three sections: positive;negative;zero.

Q: Unit format with prefix?
A: "0.0\" kg\"" — escape quotes to include literal text. Or use a custom format with backslashes.

Q: DAX error handling — IFERROR?
A: IFERROR(DIVIDE(a,b), 0) — returns 0 on error. Note: DIVIDE already returns BLANK on /0; IFERROR catches other errors.

Q: Why use BLANK() in measures?
A: Visual engines omit blanks automatically, giving cleaner charts. Returning 0 would plot a misleading point.

Q: What is the 'X-axis problem' in DAX measures?
A: Measures changing label or granularity require SUMMARIZECOLUMNS or MIN/MAX over a column to make the visual consistent.

Q: DISTINCTCOUNT on a numeric key — caveat?
A: Numeric keys may be interpreted as measures and force aggregation. Mark as dimension / do not summarize.

Q: Avoid bidirectional filters for RLS — why?
A: Bidirectional can leak rows when multiple paths exist. Single-direction with a security table is safer.

Q: Many-to-many via TREATAS — pattern?
A: CALCULATE([Sales], TREATAS(VALUES(Sel[Cat]), Products[Category])) — when no physical relationship exists between Sel and Products.

Q: Dynamic format string — example?
A: FORMAT_STRING = SWITCH(SELECTEDVALUE(Slicer[Unit]), "USD", "$#,##0", "EUR", "€#,##0", "#,##0")

Q: Performance: avoid DISTINCTCOUNT on large text columns?
A: Yes — DISTINCTCOUNT on free-text is expensive. Use a surrogate integer key for cardinality.

Q: Reduce cardinality of text columns?
A: Trim, UPPER, replace synonyms. Or split into multiple columns. Materialize the cleaned version as a calculated column at refresh.

Q: What is 'materialization' in DAX?
A: 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.

Q: Avoid materialization in measures?
A: Yes — prefer functions that the engine can push back into the storage engine: SUM, COUNTROWS, simple iterators over already-filtered tables.

Q: Why is COUNTROWS faster than COUNT?
A: COUNTROWS reads a single column metadata flag. COUNT must evaluate each value's non-blank status.