Here we come with top 50 SSAS/PowerBI DAX interview questions with answers.
Explain what is DAX?
DAX stands for Data Analysis Expressions, and it is the formula language simply it is a collection of functions, operators, and constants that can be used in a formula, or expression in Microsoft SQL Server Analysis Services, Power Pivot in Excel, and Power BI Desktop. Stated more simply, DAX helps you create new information from data already in your model.expressions.
Explain when do you use SUMX() instead of SUM()?
When the expressions to SUM() consits of anything else than a column name. Typically when you want to add or multiply the values in different columns:
SUMX(Orderline, Orderline[quantity], Orderline[price])
SUMX() first creates a row context over the Sales table (see 1 above). It then iterates through this table one row at a time. SUM() is optimized for reducing over column segments and is as such not an iterator.
What do you understand by new CALENDARAUTO() Function in DAX(SSAS)?
CALENDARAUTO function returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model.
Example: In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2011.
CALENDARAUTO() will return all dates between January 1, 2010 and December 31, 2011.
CALENDARAUTO(3) will actually return all dates between April 1, 2010 and March 31, 2012.
Name any 3 most useful aggregation functions DAX?
DAX has a number of aggregation functions, including the following commonly used functions:
- SUM
- AVERAGE
- MIN
- MAX
- SUMX (and other X functions)
These functions work only on numeric columns, and generally can aggregate only one column at a time. However, special aggregation functions that end in X, such as SUMX, can work on multiple columns. These functions iterate through the table, and evaluate the expression for each row.
Which are the three places where an expression can be evaluated and hence a specific context is set?
1. In a pivot table cell. Filter context is set by rows, columns, filters and slicers.
2. In a row cell (calculated column) Row context is set by the row itself.
3. In the measure calculation area of a table. No row, nor filter context is set.
Name any 3 most useful text functions in DAX?
The text functions in DAX include the following:
- CONCATENTATE
- REPLACE
- SEARCH
- UPPER
- FIXED
These text work very similarly to the Excel functions that have the same name, so if you're familiar with how Excel handles text functions, you're already a step ahead. If not, you can always experiment with these functions in Power BI, and learn more about how they behave.
How is filter context propagated through relationships?
Filter context automatically propagates following the filtering of the relationship. It always propagates from the one side of the relationship to the many side. In addition, you also have the <u>option of enabling the propagation from the many side to the one side</u>. No functions are available to force the propagation: Everything happens inside the engine in an automatic way, according to the definition of relationships in the data model.
What are Path() and PathLength() functions in DAX?
PATH(): Syntax PATH(<ID_columnName>, <parent_columnName>) Its Returns a delimited text string with the identifiers of all the parents of the current identifier, starting with the oldest and continuing until current.
PATHLENGTH(): Syntax PATHLENGTH(<path>) Its Returns the number of parents to the specified item in a given PATH result, including self.
Example: I think there is no need to explain you can understand it yourself
What is the difference between DISTINCT() and VALUES() in DAX?
Both count the distinct values, but VALUES() also counts a possible implictit virtual empty row because of non matching values in a child table. This is usually in a dimension table.
Which function should you use rather than COUNTROUWS(DISTINCT())?
DISTINCTCOUNT()
What is a pattern?
A pattern is a general reusable solution to a commonly occurring problem.In Microsoft Excel, you use patterns every day to build tables, charts, reports, dashboards, and more.
What are DAX patterns?
DAX Patterns is a collection of ready-to-use data models and formulas in DAX, which is the programming language of Power Pivot. Create your Excel data model faster by using a DAX pattern!
Explain RELATED() and RELATEDTABLE()?
RELATED works when you have a row context on the table on the many side of a relationship. RELATEDTABLE works if the row context is active on the one side of a relationship. It is worth noting that both, RELATED and RELATEDTABLE, can traverse a long chain of relationships to gather their result; they are not limited to a single hop.
Explain how a pivot table can be viewd as an MVC system?
1. Model = the Data Model (incl DAX expressions)
2. View = the table (or chart)
3. Controller = rows + columns + filters + slicers
What can you say about automatic filter propagation The filters only ever automatically flow from the "one" side of the relationship to the "many" side of the relationship; from the "arrow" side to the "dot" side; from the lookup table to the data table—whatever terms you use, it's always downhill.
With the lookup tables above and the data tables below, it is a mental cue to help you instantly visualize how automatic filter propagation works.
How does CALCULATE() result in context transition?
1. When in row context it transitions to filter context: the filter on the rows of a specific table propagates through the relationship to the related before the calculation is completed.
E.g. CALCULATE(SUM(OtherTable[column]) in a calculated column.
2. It extends or modifies an existing filter context, by adding a filter as its second parameter.
CALCULATE() always introduces filter context.
What is the difference between MAX and MAXA functions in DAX?
The MAX function takes as an argument a column that contains numeric values. If the
column contains no numbers, MAX returns a blank. If you want to evaluate values that
are not numbers, use the MAXA function.
How are row contexts created?
1. Automatically in a calculated column
2. Programmatically by using iterators.
How are filter contexts created?
1. Automatically by using fields on rows, columns, slicers, and filters.
2. Programmatically by using CALCULATE()
How can you propagate row context through relationships?
Propagation happens manually by using RELATED() and RELATEDTABLE(). These functions need to be used on the correct side of a one-to-many relationship: RELATED() on the many side, RELATEDTABLE() on the one side.
How does SUMMARIZECOLUMNS relate to filtering?
1. SUMMARIZECOLUMNS is not susceptible for outer (external) filters, in contrast to SUMMARIZE
2. you can add a filter (e.g. using FILTER) as a param of SUMMARIZCOLUMNS and it will filter accordingly. It acts as if you've added a filter in a pivot table.
What is the initial filter context?
The initial filter context comes from four areas of a pivot table:
1. Rows
2. Columns
3. Filters
4. Slicers
It is the standard filtering coming from a pivot table before any possible modifications from DAX formulas using CALCULATE().
How to optimize DAX query plan?
Ans: click
here
Why don't you use a CALCULATE() in the aggregation expression of a SUMMARIZECOLUMN()?
The CALCULATE() is automatically generated.
What is the difference between having a measure as a second FILTER param and having the original measure expression as a param. FILTER(table, [MEASURE]) vs FILTER(table, SUM(...))?
A measure has always implicit filter context, so the ROW context induced by the FILTER is transferred to filter context in the measure.<br>In case of the expression only, no filter context is imposed on the expression, so the expression is evaluated with an empty filter context! This gives a different result.
DAX nested Functions is equivalent to SQL what?
SQL subqueries
