What is DAX?
DAX (Data Analysis Expressions) is the formula language of Power BI, Analysis Services and Power Pivot in Excel. It works on tables and columns, not cells, and a measure's result depends on the filters around it: the same
Total Salesgives one number per bar of a chart. Function reference: DAX on Microsoft Learn.What is the difference between a measure and a calculated column?
A calculated column is computed once per row when the model refreshes and is stored, so it costs memory, and you can put it on an axis or in a slicer. A measure is computed when a visual asks for it, once per cell, in that cell's filter context, and stores nothing.
-- calculated column on Sales: one stored value per row Line Total = Sales[Quantity] * Sales[Unit Price] -- measure: one value per cell of a visual Total Sales = SUM ( Sales[Line Total] )A ratio such as margin percent must be a measure: the average of row level percentages is not the margin of the total.
What is the difference between row context and filter context?
Filter context is the set of filters that applies when a value is computed: the row and column of a matrix cell, slicers, and report, page and visual filters. Row context means "the current row", and exists in calculated columns and inside iterators such as
SUMX. Row context does not filter anything:SUM ( Sales[Amount] )in a calculated column returns the grand total on every row, untilCALCULATEturns the row into a filter.What is the difference between SUM and SUMX?
SUMadds up one column.SUMXgoes through a table row by row, evaluates an expression for each row and adds the results, so it can multiply columns without a stored calculated column:Revenue = SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )SUM ( Sales[Amount] )is exactlySUMX ( Sales, Sales[Amount] ). The same pairs exist for the other aggregations:AVERAGEX,MINX,MAXX,COUNTX.What is the difference between COUNT, COUNTA, COUNTROWS and DISTINCTCOUNT?
COUNTcounts the non blank values in a column of numbers, dates or text;COUNTAdoes the same and also counts true/false values.COUNTROWScounts the rows of a table, blanks included, and is the usual way to count facts:COUNTROWS ( Sales ).DISTINCTCOUNTcounts the distinct values of a column, counting blank as one value;DISTINCTCOUNTNOBLANKleaves blank out.Why use DIVIDE instead of the / operator?
DIVIDE ( a, b )returns BLANK whenbis zero or blank (or a third argument you give it), wherea / breturns infinity or NaN and shows an error-looking value in the visual. BLANK also hides empty rows in visuals, which an infinity does not. Use/only when the denominator is a constant or can never be zero.How does BLANK behave in DAX?
BLANK acts like zero in addition (
BLANK () + 1is 1) and stays BLANK in multiplication.BLANK () = 0is true, while the strictBLANK () == 0is false. Visuals hide a row when every measure in it is blank, so returning 0 instead of BLANK makes every product, day and customer combination appear, slows the visual down and buries the real rows. Return BLANK unless the reader needs to see a zero.Why use variables (VAR and RETURN) in DAX?
Variables name a step, so the measure reads top to bottom, and each is evaluated once, so a repeated expression is not computed twice.
Margin % = VAR Revenue = [Total Sales] VAR Cost = SUMX ( Sales, Sales[Quantity] * RELATED ( Product[Unit Cost] ) ) RETURN DIVIDE ( Revenue - Cost, Revenue )A variable is a constant once defined: it keeps the value from the context where it was defined, so using it inside a later
CALCULATEdoes not recompute it under the new filters.What is the difference between VALUES and DISTINCT?
Both return the distinct values of a column in the current filter context.
VALUESalso returns the blank row that the engine adds when the fact table has keys missing from the dimension (shown as "(Blank)" in visuals);DISTINCTdoes not. On a whole table,VALUES ( Product )keeps that blank row too.ALLincludes it as well, andALLNOBLANKROWleaves it out.What do HASONEVALUE and SELECTEDVALUE do?
HASONEVALUE ( col )is true when the filter context leaves exactly one value of the column.SELECTEDVALUE ( col, alternate )returns that one value, or the alternate when there are none or several. They drive dynamic titles, what-if parameters and measures that should only show at one level:Report Title = "Sales for " & SELECTEDVALUE ( Product[Category], "all categories" )How do you rank products by sales with RANKX?
Rank over all products, not only the current one:
Product Rank = RANKX ( ALL ( Product[Product] ), [Total Sales] )Without
ALL, each row of the visual ranks its product against itself and every rank is 1. UseALLSELECTEDinstead to rank within the products a slicer leaves. Ties share a rank and the next rank is skipped by default (1, 1, 3); passDENSEas the last argument for 1, 1, 2.
DAX Interview Questions and Answers
Measures and calculated columns, row and filter context, aggregators and iterators, BLANK, and the everyday DAX functions.