POWER BI
DAX
30 Interview
questions
1. What is DAX in power BI?
DAX (Data Analysis Expressions) is a formula language used in Power BI,
Power Pivot, and Analysis Services to create custom calculations and
perform advanced data manipulation. It combines functions, operators,
and constants to work on data in tables and columns..
2. What is the difference between calculated columns and measures
in DAX?
Calculated Columns are computed row by row and stored in the
dataset. They increase storage usage but make filtering easier.
Measures are calculated on the fly and are not stored in the dataset.
They save storage space but are recalculated each time they are used
in visualizations and reports.
3. Explain the function CALCULATE in DAX.
The CALCULATE function modifies the context in which an expression is
evaluated by applying specific filters. It is a powerful function for context
modification in DAX.
4. How does the ALL function work in DAX?
The ALL function removes filters from a column or table, returning all
rows regardless of any filters applied. It’s useful for calculations that need
the entire dataset, like "percent of total."
5. What is the purpose of the RELATED function in DAX?
The RELATED function retrieves values from a related table by using
relationships between tables. It’s commonly used in models where tables
are connected by relationships.
6. Explain the difference between DISTINCT and VALUES functions in
DAX.
DISTINCT returns a unique list of values from a column or table.
VALUES also returns unique values but can return blank when there
are no rows in the context. It’s more versatile for handling blanks and
filters.
7. How do you create a running total in DAX?
A running total can be created using the SUMX function along with
EARLIER or FILTER. It iterates through each row and sums values up to
that point.
8. What is the purpose of the FILTER function in DAX?
The FILTER function returns a table that represents a subset of another
table based on a logical condition. It is often used within CALCULATE or
other table manipulation functions to limit the dataset.
9. Explain the use of EARLIER in DAX.
EARLIER refers to the row context of an earlier row. It allows nested row
operations, such as creating calculated columns that depend on previous
rows.
10. How do you use IFERROR in DAX?
The IFERROR function checks for errors in an expression. If an error is
found, it returns an alternative value, preventing issues like division by
zero.
11. What is a calculated column in DAX?
A calculated column is a new column added to a table using a DAX
formula. It is calculated row-by-row and stored in the dataset, unlike
measures, which are calculated dynamically.
12. What is the difference between SUM and SUMX in DAX?
SUM adds up all values in a column, whereas SUMX iterates through each
row, evaluates an expression for each row, and sums the results. SUMX is
useful when calculations need to be performed on each row before
summing.
13. How does the RANKX function work in DAX?
RANKX ranks items in a table based on an expression or column value. It
is often used to create ranking measures for reports or visualizations.
14. What is a context transition in DAX?
A context transition occurs when a row context is converted into a filter
context. This happens most often in calculated columns and measures,
especially when using the CALCULATE function.
15.What is a table function in DAX? Give examples.
Table functions return entire tables, not individual values. Examples
include FILTER, ALL, DISTINCT, and VALUES. These functions are used for
table manipulations and can be combined with aggregation functions
like SUMX or AVERAGEX.
16. How do you calculate year-to-date (YTD) totals in DAX?
The TOTALYTD function is used to calculate the year-to-date total for a
measure. It aggregates values from the beginning of the year up to the
current context date.
17. Explain the use of USERELATIONSHIP in DAX.
USERELATIONSHIP is used to activate an inactive relationship between
tables when there are multiple relationships. It activates this relationship
for the duration of the calculation.
18. How do you handle many-to-many relationships in Power BI?
Many-to-many relationships can be handled by using intermediary or
bridge tables. DAX functions like CROSSJOIN can be used to calculate
across multiple tables in such cases.
19. What is a row context in DAX?
Row context refers to the current row during the evaluation of a DAX
expression. It is the context used when DAX operates row by row, such as
in calculated columns.
20.What is a filter context in DAX?
Filter context refers to the set of filters applied to the data model during
evaluation. This includes filters from visuals, slicers, and DAX functions like
CALCULATE.
21. What does the CONTAINS function do in DAX?
The CONTAINS function checks if a value exists in a column or table. It
returns TRUE if the value is found; otherwise, it returns FALSE.
22. How can you create a percentage of total in DAX?
To calculate the percentage of total, use the DIVIDE function. First,
calculate the total sum of the values, then divide each individual value by
the total.
23. What is the difference between BLANK and NULL in DAX?
In DAX, BLANK represents a missing or empty value, while NULL is not
explicitly used. BLANK values are handled using functions like ISBLANK or
COALESCE.
24. What is the significance of the PATH function in DAX?
The PATH function returns a string representing the hierarchy path
between parent and child items. It is used in parent-child hierarchies to
navigate relationships.
25. How do you handle errors in DAX?
Errors in DAX can be handled using the IFERROR function, which returns
an alternative value when an error occurs, preventing issues like division
by zero.
26. What is the difference between NATURALINNERJOIN and
NATURALLEFTOUTERJOIN in DAX?
NATURALINNERJOIN returns rows common to both tables, while
NATURALLEFTOUTERJOIN returns all rows from the left table and
matching rows from the right table.
27. How can you optimize DAX queries for performance?
Optimizing DAX queries involves reducing the number of calculations,
limiting row-based operations, using SUMX instead of FILTER with SUM,
and minimizing the use of complex filters.
28. What is the purpose of the GENERATE function in DAX?
The GENERATE function combines tables by performing a Cartesian
product. It is useful for generating all possible combinations of rows from
two or more tables.
29. How do you use the SWITCH function in DAX?
The SWITCH function is similar to a case or switch statement in
programming languages. It evaluates an expression and returns a result
based on matching conditions.
30. What are time intelligence functions in DAX?
Time intelligence functions perform calculations based on date ranges.
Examples include TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD,
which help in analyzing time-based data.
LIKE FOLLOW REPOST
Mohammed Iqram Rahman Shariff
