30.4 DAX

Q25. What is DAX?

Data Analysis Expressions – the formula language used in Power BI, Analysis Services and Power Pivot for calculated columns, measures, calculated tables and security rules.

Q26. Calculated column vs measure?

A calculated column is computed row by row at refresh and stored in the model; it can be used in slicers and axes. A measure is computed at query time based on the current filter context and not stored; used for aggregations and KPIs. Prefer measures for aggregations.

Q27. What are row context and filter context?

Row context is the current row during iteration (in calculated columns and X functions). Filter context is the set of filters (from visuals, slicers, filters and CALCULATE) that determines which rows are visible when a measure is evaluated.

Q28. What is context transition?

When CALCULATE is evaluated inside a row context, it converts the current row into an equivalent filter context. Measure references are implicitly wrapped in CALCULATE, so referencing a measure in a calculated column or iterator triggers context transition.

Q29. What does CALCULATE do?

It evaluates an expression in a modified filter context – adding, replacing or removing filters with its filter arguments and modifiers (ALL, REMOVEFILTERS, KEEPFILTERS, USERELATIONSHIP, CROSSFILTER). It is the most important DAX function.

Q30. SUM vs SUMX?

SUM aggregates a single column. SUMX iterates a table, evaluates an expression for each row (e.g. Orders[Quantity] * RELATED(Product[Unit Price])), then sums the results.

Q31. COUNT vs COUNTROWS vs DISTINCTCOUNT?

COUNT counts non-blank values in a column; COUNTROWS counts the rows of a table (or table expression); DISTINCTCOUNT counts unique values in a column. Example: in an order-line table, COUNTROWS(Orders) gives order lines, while DISTINCTCOUNT(Orders[Order ID]) gives orders.

Q32. ALL vs ALLEXCEPT vs ALLSELECTED vs REMOVEFILTERS?

ALL removes filters from a table or columns (and can return a table). ALLEXCEPT removes all filters from a table except those on specified columns. ALLSELECTED removes filters coming from inside the visual but keeps outside filters like slicers – used for "% of visible total". REMOVEFILTERS works like ALL but only as a CALCULATE modifier and is more readable.

Q33. FILTER vs CALCULATE filter arguments?

FILTER is an iterator that returns a table of rows meeting a condition – necessary for conditions on measures or complex logic. Simple column predicates in CALCULATE (e.g. Product[Category] = "Snacks") are more efficient. Filter columns, not whole tables.

Q34. RELATED vs RELATEDTABLE vs LOOKUPVALUE?

RELATED returns a value from the one side of a relationship (used on the many side). RELATEDTABLE returns the related rows from the many side (used on the one side). LOOKUPVALUE finds a value based on search conditions without needing a relationship.

Q35. What is SELECTEDVALUE used for?

It returns the value of a column when exactly one value is in the filter context, otherwise an alternate result. Common for dynamic titles and parameter-driven logic, e.g. SELECTEDVALUE(DarkStore[City], "All Cities").

Q36. Why use DIVIDE instead of the / operator?

DIVIDE safely handles division by zero, returning BLANK or a specified alternate result instead of an error or infinity.

Q37. How do you calculate YTD and previous-year sales?

TOTALYTD([Total Sales], 'Date'[Date]) or CALCULATE([Total Sales], DATESYTD('Date'[Date])) for YTD, and CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])) for the same period last year. YoY % = DIVIDE(current − LY, LY).

Q38. DATEADD vs PARALLELPERIOD vs SAMEPERIODLASTYEAR?

SAMEPERIODLASTYEAR shifts the visible dates back one year. DATEADD shifts dates by any number of days, months, quarters or years. PARALLELPERIOD returns the full period(s) at the chosen level – e.g. the whole previous year even if only March is selected.

Q39. How do you create a running total?

CALCULATE([Total Sales], FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]))). For a total that restarts each year, use DATESYTD/TOTALYTD.

Q40. How does RANKX work?

RANKX(table, expression, [value], [order], [ties]) evaluates the expression for each row of the table and returns the rank of the current value. Use ALL on the table so all items are compared, and choose SKIP or DENSE for ties.

Q41. What are variables in DAX and their benefits?

Declared with VAR and returned with RETURN. They improve readability, allow easy debugging, and improve performance because each variable is evaluated only once. A variable's value is fixed where it is defined.

Q42. What is the difference between VALUES and DISTINCT?

Both return distinct values of a column. VALUES can include an extra blank row when there are fact rows with no matching dimension row (referential integrity violation); DISTINCT does not add this blank row.

Q43. How would you calculate Average Order Value (AOV) and Cancellation Rate?

AOV = DIVIDE([Total Sales], [Delivered Orders]). Cancellation Rate = DIVIDE([Cancelled Orders], [Total Orders]), where the order counts use DISTINCTCOUNT(Orders[Order ID]) with a CALCULATE filter on Order Status.

Q44. Your fact table is at order-line level but delivery time is an order-level value. How do you average it correctly?

Take one value per order and then average across orders: AVERAGEX(VALUES(Orders[Order ID]), CALCULATE(MAX(Orders[Delivery Time Mins]))). A plain AVERAGE over the column would give orders with many lines more weight.

Q45. How do you compare two platforms (e.g. Blinkit vs Amazon Now) in one model?

Append both sources into one Orders table with a Platform column (same column names), then use Platform in slicers/legends – for example, comparing Blinkit and Amazon Now order counts in Pune or Nagpur – or measures such as CALCULATE([Total Orders], Orders[Platform] = "Blinkit").

DAX answers lihitana ghaai karu naka – ek ek line bolat lihaychi savay laava.