Most Important DAX Interview Questions and Answers

Here are some important DAX interview questions, along with detailed answers and examples:

1. What is DAX, and why is it used in Power BI?

DAX (Data Analysis Expressions) is a formula language used in Microsoft Power BI, SQL Server Analysis Services (SSAS), and Power Pivot for Excel. It is designed to handle data analysis and calculations. DAX enables users to create custom calculations and business logic that can be used in their reports and data models.

Example:

Creating a measure to calculate total sales:

Total Sales = SUM(Sales[Sales Amount])

2. What is the difference between a calculated column and measure?

Calculated Column: Calculated columns are computed during data load and are stored in the data model. They can be used like any other column in the model.

Sales[Total Cost] = Sales[Quantity] * Sales[Unit Price]

Measure: Measures are calculated on the fly based on user interactions with the report (like slicers and filters). Measures are not stored in the model, they are recalculated as needed.

Total Sales = SUM(Sales[Sales Amount])

3. Explain the use of the CALCULATE function in DAX with an example.

The CALCULATE function evaluates an expression in a modified filter context. It is often used to change the context of calculations based on specific conditions.

Example:
Calculating total sales for the INDIA:

Total Sales USA = CALCULATE(
SUM(Sales[Sales]),
Sales [Country] = "India"
)

4. What does the ALL function do in DAX, and when would you use it?

The ALL function removes all filters from the specified columns or tables. It is commonly used to create measures that need to ignore certain filters, such as creating a grand total or calculating percentages.

Example:
Calculating the percentage of total sales:

Total Sales = SUM(Sales[Sales Amount])

Sales Percentage = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales)))

5. What is the difference between the SUM and SUMX functions in DAX?

SUM: The SUM function adds up all the values in a single column.

Total Sales = SUM(Sales[Sales Amount])

SUMX: The SUMX function iterates over a table, evaluates an expression for each row, and then sums the results.

Total Sales Calculated =
SUMX(Sales, Sales[Quantity] * Sales[Unit Price])

6. What is the difference between RELATED and RELATEDTABLE in DAX?

RELATED: The RELATED function retrieves a related value from another table based on an existing relationship.

Sales[Product Category] = RELATED(Product[Category])

RELATEDTABLE: The RELATEDTABLE function returns a table containing all rows that are related to the current row in the primary table.

Total Sales by Product = SUMX(RELATEDTABLE(Sales), Sales[Sales Amount])

7. How can you handle errors in DAX calculations?

Errors in DAX can be handled using the IFERROR function, which allows you to specify a value or expression to return if an error is found.

Sales Per Unit = 
IFERROR(
[Total Sales] / [Units Sold],
0
)

8. Explain the use of the SUMMARIZECOLUMNS function in DAX with an example.

The SUMMARIZECOLUMNS function is used to create a summary table for the requested totals over a set of groups.

Example:
Creating a summary table of total sales by product and year:

Summary Table = SUMMARIZECOLUMNS(
Sales[Product],
Sales[Year],
"Total Sales", SUM(Sales[Sales Amount])
)

9. What is the difference between the VALUES and DISTINCT functions in DAX?

VALUES: Returns a one-column table that contains the distinct values from the specified column, but also includes a blank row if any blank values are present.

Unique Products = VALUES(Sales[Product])

DISTINCT: Returns a one-column table that contains the unique values from the specified column, excluding any blank values.

Unique Products = DISTINCT(Sales[Product])

10. Explain the difference between the FILTER and KEEPFILTERS functions in DAX.

FILTER: Returns a table with only the rows that meet the criteria. It overwrites any existing filters on the columns involved.

Filtered Sales = CALCULATE([Total Sales], FILTER(Sales, Sales[Quantity] > 10))

KEEPFILTERS: Modifies the filter context of a calculation without overwriting existing filters on the columns involved.

Filtered Sales = CALCULATE([Total Sales], KEEPFILTERS(Sales[Quantity] > 10))

11. How can you create a dynamic title in Power BI using DAX?

A dynamic title can be created using a measure that changes based on the filter context or slicer selection.

Example:
Creating a dynamic title based on a selected year:

Selected Year = SELECTEDVALUE(Sales[Year], "All Years")

Dynamic Title = "Sales Report for " & [Selected Year]

12. How can you calculate a moving average in DAX?

A moving average can be calculated using the AVERAGEX function along with DATESINPERIOD to define the period over which to average.

Example: Calculating a 3-month moving average of sales:

Moving Average: 3 Months = 
CALCULATE(AVERAGEX(
DATESINPERIOD(
Sales[Date], MAX(Sales[Date]), -3, MONTH),
[Total Sales]))

13. Explain how to calculate cumulative totals in DAX with an example.

Cumulative totals can be calculated using the CALCULATE function along with FILTER to sum values up to the current date.

Example: Calculating cumulative sales:

Cumulative Sales = 
CALCULATE([Total Sales],
FILTER(ALL(Sales[Date]), Sales[Date] <= MAX(Sales[Date])
))

14. What is the ISINSCOPE function in DAX, and how is it used?

The ISINSCOPE function returns TRUE if the specified column is in the scope of the current evaluation context. It is useful for writing dynamic measures that behave differently based on the hierarchy level in a visual.

Example: Creating a measure that behaves differently based on the hierarchy level:

Dynamic Measure = 
IF(
ISINSCOPE(Sales[Product]),
[Product Level Calculation],
[Category Level Calculation]
)

15. What is the USERELATIONSHIP function in DAX, and when would you use it?

The USERELATIONSHIP function is used to activate an inactive relationship for a specific calculation. It allows you to use a relationship that is not the default active relationship in the data model.

Example:
Using USERELATIONSHIP to calculate sales by a secondary date:

Sales by Ship Date = 
CALCULATE(
[Total Sales],
USERELATIONSHIP(Sales[Ship Date], Calendar[Date])
)

16. Explain the SELECTEDVALUE function in DAX and provide an example.

The SELECTEDVALUE function returns the value of a column if there is only one value selected, otherwise, it returns the specified alternate result or blank.

Example: Getting the selected year from a slicer:

Selected Year = SELECTEDVALUE(Sales[Year], "No Year Selected")

17. Explain how the IN operator works in DAX and provide an example.

The IN operator checks if a value exists in a list of values. It is useful for filtering data based on multiple criteria.

Example: Filtering sales for specific products:

Filtered Sales = 
CALCULATE(
[Total Sales],
Sales[Product] IN {"Product A", "Product B", "Product C"}
)

18. Explain the GROUPBY function in DAX and provide an example.

The GROUPBY function returns a table grouped by specified columns, allowing you to perform aggregations on the grouped data.

Example: Grouping sales by product and calculating total sales:

GroupedSales = GROUPBY(
Sales,
Sales[Product],
"Total Sales", SUMX(CURRENTGROUP(), Sales[Sales Amount])
)

19. What does the ADDCOLUMNS function do in DAX, and provide an example.

The ADDCOLUMNS function adds calculated columns to a table. It evaluates an expression for each row in the table and adds the results as new columns.

Example: Adding a calculated column for profit:

SalesWithProfit = ADDCOLUMNS(
Sales,
"Profit", Sales[Sales Amount] - Sales[Cost]
)

20. Explain the purpose of the FORMAT function in DAX and provide an example.

The FORMAT function converts a value to text according to the specified format. It is useful for creating human-readable values in specific formats, such as currency or dates.

FORMAT(TODAY(), "MM/DD/YYYY")

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.