IT2EDU Empowering Education Through Technology
Related Articles
These are the mostly asked DAX Interview questions in and Power BI developer Interviews for all levels.
Q: Can you explain the difference between the EARLIER and RELATED functions in DAX?
Ans:
In DAX, EARLIER and RELATED are both functions that can be used to work with related tables in a data model.
EARLIER() function references the value of a column from an earlier row context. It can be used to access a value from a previous row when working with calculated columns and tables in DAX. This function is typically used in combination with iterators like SUMX, AVERAGEX, and MAXX to perform calculations on previous rows in a table.
For example, consider a table of sales transactions where we want to calculate the difference between the current transaction amount and the previous transaction amount for a particular customer:
=CALCULATE(SUM(Sales[Amount]) - EARLIER(SUM(Sales[Amount])), FILTER(Sales, Sales[Customer] = EARLIER(Sales[Customer])))
The RELATED function, on the other hand, returns a related value from another table. It is typically used in a context where there is a relationship between two tables, and you want to access a column value from the related table.
For example, suppose you have two tables in your data model: Customers and Sales. If you want to calculate the total sales amount for a customer, you can use the RELATED function to get the customer ID from the Sales table and then use it to look up the customer name from the Customers table:
=SUM(Sales[Amount]) & " - " & RELATED(Customers[Name])
So, to summarize: EARLIER() is used to reference a previous row context, while RELATED() is used to access related values from another table.
Q: How would you implement a rolling average calculation in DAX?
Ans: To implement a rolling average calculation in DAX, you can use the AVERAGEX function in combination with an iterator function such as FILTER, to iterate over a specified number of rows and calculate the average of a column.
Here are few examples:
In this example, how to calculate a rolling average of sales amount for the past 3 months:
=CALCULATE(
AVERAGEX(
FILTER(
Sales,
Sales[Date] > EARLIER(Sales[Date]) - 90 &&
Sales[Date] <= EARLIER(Sales[Date])
),
Sales[Amount]
),
FILTER(
Sales,
Sales[Date] <= EARLIER(Sales[Date])
)
)
In this formula, we are using the CALCULATE function to apply the filter context to the Sales table. The AVERAGEX function is used to calculate the average of the Sales[Amount] column over the past 3 months. The FILTER function is used to filter the Sales table for the previous 3 months based on the current row context. The EARLIER function is used to reference the previous row context to calculate the rolling average.
Note that this formula assumes that there is a Date column in the Sales table, which can be used to filter the data based on a rolling time period. You may need to modify the formula depending on your specific data model and requirements.
in this second example, how you could calculate a rolling average of sales over the last 7 days:
Rolling 7-day Average =
AVERAGEX(
DATESINPERIOD(
'Calendar'[Date],
MAX('Calendar'[Date]) - 6, // start date for rolling period
MAX('Calendar'[Date]), // end date for rolling period
DAY
),
CALCULATE(
SUM('Sales'[Amount]),
FILTER(
'Sales',
'Sales'[Date] <= EARLIER('Calendar'[Date]) && 'Sales'[Date] > EARLIER('Calendar'[Date]) - 7
)
)
)
In this example, we’re using the DATESINPERIOD function to define the rolling 7-day period, starting from the current date and going back 6 days. We then use the AVERAGEX function to iterate over each day in the rolling period and calculate the average of sales amounts for that day and the preceding 6 days.
The CALCULATE function is used to filter the Sales table based on the date range defined by the current day and the preceding 6 days, and the EARLIER function is used to reference the current day in the iteration.
Note that this is just one example of how you could calculate a rolling average in DAX, and the specific implementation may vary depending on your data model and requirements.
In this 3rd example of how you can calculate a rolling 3-month average of sales for each product in a Sales table:
=CALCULATE(
AVERAGEX(
FILTER(
Sales,
Sales[Product] = EARLIER(Sales[Product]) &&
Sales[Date] <= EARLIER(Sales[Date]) && Sales[Date] > EARLIER(Sales[Date]) - 90
),
Sales[Amount]
),
ALLEXCEPT(Sales, Sales[Product], Sales[Date])
)
In this example, we first use the FILTER function to create a moving window of three months for each product and date combination. The window is defined using three conditions:
- The product must be the same as the product in the current row context (using EARLIER(Sales[Product])).
- The date must be less than or equal to the date in the current row context (using EARLIER(Sales[Date])).
- The date must be greater than the date in the current row context minus 90 days (i.e., a three-month window).
Next, we use the AVERAGEX function to calculate the average of the Amount column within the moving window.
Finally, we use the CALCULATE function with the ALLEXCEPT function to remove all filters except for the product and date columns, so that the rolling average is calculated separately for each product and date combination.
Note that the rolling average calculation can be resource-intensive, particularly for large datasets, so it’s important to optimize the DAX formula and ensure that it runs efficiently.
Q: What is the role of the VALUES function in DAX?
Ans:
The VALUES function in DAX is used to return a table that contains a single column of unique values from the specified column expression.
The VALUES function is particularly useful when you want to display a list of distinct values from a column in a visual or table. For example, if you have a Sales table with a Product column and you want to display a list of distinct products in a drop-down list, you can use the VALUES function to create a table of unique products.
Here’s an example:
Product List = VALUES(Sales[Product])
In this example, the VALUES function is used to create a table of unique products from the Sales table’s Product column. This table can be used to create a list of distinct products in a visual or table.
The VALUES function is also commonly used in combination with other DAX functions, such as CALCULATE and FILTER, to create more complex calculations that depend on unique values from a column expression.
Note that the VALUES function may return unexpected results when used in certain contexts, such as when there are multiple filter contexts or when used with calculated columns that depend on other columns in the same table. Therefore, it’s important to test your DAX formulas thoroughly and understand the behavior of the VALUES function in different scenarios.
Q: Can you explain the difference between the RANKX and TOPN functions in DAX?
Ans:
The RANKX and TOPN functions in DAX are both used to rank items within a table based on a specific criteria. However, they differ in how they rank the items and in the type of output they produce.
The RANKX function assigns a rank to each item in a table based on a specified expression. It returns a rank value for each item, where the highest ranked item receives a rank of 1, the next highest ranked item receives a rank of 2, and so on. If two or more items have the same rank, they are assigned the same rank value and the next rank is skipped.
Here’s an example of using RANKX to rank customers by their total sales:
Rank = RANKX(ALLSELECTED(Sales[Customer]), CALCULATE(SUM(Sales[Amount])))
In this example, the RANKX function is used to rank customers by their total sales amount, where the highest total sales amount receives a rank of 1, the next highest receives a rank of 2, and so on.
On the other hand, the TOPN function returns the top N items from a table based on a specified expression. It returns a new table that contains the top N items, where N is specified as a parameter to the function. If there are ties for the last item in the top N, all the tied items are included in the output.
Here’s an example of using TOPN to return the top 10 products by sales:
Top 10 Products = TOPN(10, VALUES(Sales[Product]), CALCULATE(SUM(Sales[Amount])))
In this example, the TOPN function is used to return the top 10 products by their total sales amount, where the output is a table that contains the top 10 products.
To summarize, RANKX assigns a rank to each item in a table based on a specified expression, while TOPN returns the top N items from a table based on a specified expression.
Q: How do you implement parameter tables in DAX?
Ans:
Parameter tables in DAX are a powerful tool that allow you to define input parameters to control the behavior of your DAX formulas. They can help you create flexible and reusable calculations that can be easily customized for different scenarios. In this article, we’ll walk you through the steps to implement parameter tables in DAX.
To create a parameter table in DAX, you can follow these steps:
- Create a new table in your data model that will serve as the parameter table. This table should contain one column for each parameter you want to define.
- Populate the parameter table with the values you want to use as input for your DAX formulas. You can either manually enter the values or load them from another data source.
- Use the SELECTEDVALUE function to retrieve the value of the parameter in your DAX formula. The SELECTEDVALUE function returns the value of the specified column if there is only one value selected in the column, or a default value if there are no values or multiple values selected.
Here’s an example of a parameter table that defines two input parameters for a DAX formula:
ParameterTable:
PARAMETER VALUE
———————-
StartDate 1/1/2022
EndDate 12/31/2022
And here’s an example of how you can use the parameter table in a DAX formula to calculate the sales amount for a given period:
SalesAmount = CALCULATE(SUM(Sales[Amount]),
FILTER(Sales,
Sales[Date] >= SELECTEDVALUE(ParameterTable[StartDate]) &&
Sales[Date] <= SELECTEDVALUE(ParameterTable[EndDate])))
In this example, the SELECTEDVALUE function is used to retrieve the values of the StartDate and EndDate parameters from the ParameterTable, and the FILTER function is used to filter the Sales table based on the selected date range.
In conclusion, parameter tables in DAX provide a flexible and powerful way to define input parameters for your DAX formulas. By following the steps outlined in this article, you can easily implement parameter tables in your data model and use them to create flexible and customizable calculations.
