Menu
CoddyTech

DAX Interview Questions and Answers

Measures and calculated columns, row and filter context, aggregators and iterators, BLANK, and the everyday DAX functions.

12 questions
  1. 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 Sales gives one number per bar of a chart. Function reference: DAX on Microsoft Learn.

  2. 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.

  3. 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, until CALCULATE turns the row into a filter.

  4. What is the difference between SUM and SUMX?

    SUM adds up one column. SUMX goes 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 exactly SUMX ( Sales, Sales[Amount] ). The same pairs exist for the other aggregations: AVERAGEX, MINX, MAXX, COUNTX.

  5. What is the difference between COUNT, COUNTA, COUNTROWS and DISTINCTCOUNT?

    COUNT counts the non blank values in a column of numbers, dates or text; COUNTA does the same and also counts true/false values. COUNTROWS counts the rows of a table, blanks included, and is the usual way to count facts: COUNTROWS ( Sales ). DISTINCTCOUNT counts the distinct values of a column, counting blank as one value; DISTINCTCOUNTNOBLANK leaves blank out.

  6. Why use DIVIDE instead of the / operator?

    DIVIDE ( a, b ) returns BLANK when b is zero or blank (or a third argument you give it), where a / b returns 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.

  7. How does BLANK behave in DAX?

    BLANK acts like zero in addition (BLANK () + 1 is 1) and stays BLANK in multiplication. BLANK () = 0 is true, while the strict BLANK () == 0 is 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.

  8. 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 CALCULATE does not recompute it under the new filters.

  9. What is the difference between VALUES and DISTINCT?

    Both return the distinct values of a column in the current filter context. VALUES also returns the blank row that the engine adds when the fact table has keys missing from the dimension (shown as "(Blank)" in visuals); DISTINCT does not. On a whole table, VALUES ( Product ) keeps that blank row too. ALL includes it as well, and ALLNOBLANKROW leaves it out.

  10. 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" )
    
  11. 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. Use ALLSELECTED instead to rank within the products a slicer leaves. Ties share a rank and the next rank is skipped by default (1, 1, 3); pass DENSE as the last argument for 1, 1, 2.

Coddy programming languages illustration

Learn to code with Coddy

Get started