Power BI Using DAX Interview Questions Quiz

Test your understanding of key DAX concepts, syntax, and functions used in Power BI data modeling. This quiz covers context manipulation, calculations, time intelligence, filtering, and common DAX functions to help you assess and deepen your DAX skills.

1. Calculating Total Sales

   Which DAX function should you use to calculate the total sales amount from the Sales[SalesAmount] column?

   1. MIN
   2. SUM
   3. SUMX
   4. COUNT

   Explanation: The SUM function adds up all numeric values in a column, making it ideal for calculating total sales. SUMX, while similar, is used for evaluating expressions row by row. COUNT only counts the number of rows or items, not their numeric total. MIN returns the smallest value, not the total.

2. SUM vs. SUMX

   What is the primary difference between the SUM and SUMX functions in DAX when calculating totals?

   1. SUM ignores blanks; SUMX does not.
   2. SUM counts rows; SUMX finds maximums.
   3. SUM requires a filter; SUMX does not.
   4. SUM operates on columns; SUMX can evaluate row-by-row expressions.

   Explanation: SUM only adds up the numbers within a single column, while SUMX can compute an expression for each row and then sum those results. SUM does not require a filter, nor does SUMX specifically find maximums. Both SUM and SUMX have similar behavior regarding blanks.

3. Year-to-Date Sales

   Which DAX function would you use to calculate year-to-date (YTD) sales totals?

   1. TOTALYTD
   2. AVERAGE
   3. PARALLELPERIOD
   4. FIRSTDATE

   Explanation: TOTALYTD is specifically designed for calculating cumulative totals from the start of the year up to a selected date. PARALLELPERIOD shifts time periods, FIRSTDATE finds the earliest date, and AVERAGE finds the mean value rather than a cumulative total.

4. Modifying Filter Context

   If you want to change the filter context for a calculation, which DAX function would you typically use?

   1. SWITCH
   2. CALCULATE
   3. RANKX
   4. NOW

   Explanation: CALCULATE allows you to modify or override the filter context of an expression, letting you adjust what data is considered. SWITCH is for multi-condition logic, NOW provides the current date and time, and RANKX is used for ranking values.

5. Percentage of Total Sales

   How can you calculate the percentage of total sales for each product using DAX?

   1. Use the COUNT function on product sales.
   2. Subtract total sales from product sales.
   3. Multiply product sales by total sales.
   4. Divide product sales by total sales with the DIVIDE function.

   Explanation: Calculating a percentage involves dividing the part by the whole, and DIVIDE is used for this safely in DAX. Multiplying does not yield a percentage, and subtraction also does not provide the correct proportion. COUNT simply tallies rows, not percentages.

6. Moving Average Calculation

   Which combination of functions is typically used in DAX to calculate a 30-day moving average of sales?

   1. COUNTROWS and VALUES
   2. AVERAGEX and DATESINPERIOD
   3. MIN and MAX
   4. SUMMARIZE and MIN

   Explanation: AVERAGEX computes an average over an expression, and DATESINPERIOD provides the required 30-day time window. COUNTROWS and VALUES would only return counts or distinct values. SUMMARIZE and MIN, as well as MIN and MAX, do not directly provide a moving average.

7. Handling Blank Values

   When you want to replace blank values in a DAX measure with zero, which function should you use?

   1. EARLIER
   2. COALESCE
   3. MAXX
   4. RANK

   Explanation: COALESCE returns the first non-blank value in a list, making it perfect for substituting blanks. RANK provides ordering, EARLIER is for iterators and row contexts, and MAXX finds maximum values across expressions, not for handling blanks.

8. ALL vs. ALLEXCEPT

   What is the main functional distinction between the ALL and ALLEXCEPT functions in DAX?

   1. ALL aggregates data; ALLEXCEPT divides data.
   2. ALL removes all filters; ALLEXCEPT keeps specified filters.
   3. ALL keeps all filters; ALLEXCEPT adds filters.
   4. ALL sorts data; ALLEXCEPT groups data.

   Explanation: ALL completely removes filters from columns or tables, while ALLEXCEPT retains filters on specified columns. ALL does not keep filters, nor does ALLEXCEPT add new ones. Neither function aggregates or specifically groups or divides data.

9. Filtering by Time Period

   If you want to filter Sales data for the year 2021 in DAX, which approach should you use?

   1. MAX(Sales[Year]) = 2021
   2. FILTER with YEAR(Sales[Date]) = 2021
   3. SUMMARIZE(Sales, Sales[Date])
   4. RANKX(Sales[Year], Sales[SalesAmount])

   Explanation: Using FILTER with the YEAR function filters the data to only include rows from 2021. MAX retrieves the single largest year, not the filtered set. RANKX is for ranking, not filtering, and SUMMARIZE creates a summary table rather than filtering by year.

10. Running Total Calculation

    Which DAX approach should you use to compute a running total of sales for each date?

    1. MAXX with VALUES
    2. EARLIER with SUMX
    3. CALCULATE combined with FILTER and ALL functions
    4. COUNTROWS with FILTER

    Explanation: This combination allows you to recalculate the sum for all dates up to the current row, creating a running total. MAXX with VALUES is not suited for cumulative calculations, EARLIER with SUMX is more relevant for nested row contexts, and COUNTROWS simply gives a count.

11. IF Function Usage

    How does the IF function in DAX operate when analyzing values such as Sales[SalesAmount]?

    1. Calculates the total of all sales amounts.
    2. Creates a list of unique values.
    3. Filters only positive amounts.
    4. Returns one value if a condition is true, otherwise another value.

    Explanation: The IF function checks a condition and outputs one result if true and another if false. It does not filter for positive numbers, sum totals, or generate distinct lists; those are handled by other DAX functions.

12. EARLIER Function

    What is the main purpose of the EARLIER function in DAX?

    1. It removes all filters from a table.
    2. It references a value from a previous row context during iteration.
    3. It counts the number of distinct rows.
    4. It provides the earliest date in a column.

    Explanation: EARLIER allows access to a value from an outer row context when iterating through a table expression. It doesn’t return the earliest date or remove filters; DISTINCTCOUNT is used for counting distinct rows.

13. First Day of the Month

    How can you calculate the first day of any month for a given date using DAX?

    1. MIN(Date[Date])
    2. FIRSTNONBLANK(Date[Date], 1)
    3. NOW()
    4. DATE(YEAR(Date[Date]), MONTH(Date[Date]), 1)

    Explanation: This formula combines the year and month from a date, and sets the day to one, giving the first day of the month. MIN finds the earliest date in a column. NOW returns the current date and time, not related to the context. FIRSTNONBLANK finds the first non-blank value based on sort order, not always the first day.

14. Ranking Products by Sales

    Which DAX function would you use to assign a dynamic rank to products based on their total sales?

    1. COUNTAX
    2. ROW
    3. LASTDATE
    4. RANKX

    Explanation: RANKX is designed for ranking items in a table based on a value or expression such as total sales. ROW creates temporary single-row tables, COUNTAX counts non-blank values, and LASTDATE finds the latest date in a column.

15. Date Difference Calculation

    How can you calculate the number of days between Sales[StartDate] and Sales[EndDate] in DAX?

    1. MAX(Sales[EndDate] - Sales[StartDate])
    2. EARLIER(Sales[Date])
    3. DATEDIFF(Sales[StartDate], Sales[EndDate], DAY)
    4. COUNTROWS(Sales[StartDate], Sales[EndDate])

    Explanation: DATEDIFF computes the difference between two dates in units like days. Subtracting dates manually may give errors with non-sequential data. COUNTROWS counts rows, not date intervals. EARLIER is not relevant for direct date calculation.