Thursday , November 30 2023
Breaking News
Home / Computer Science / Microsoft PowerBI / DAX Interview Questions Part – 2
Dax Interview Questions

# DAX Interview Questions Part – 2

These are mostly asked DAX Interview Questions in Power BI developer or Data analyst interview’s. Below are questions for experienced level and freshers interviews.

Q. Can you explain the difference between the SUM and SUMX functions in DAX?

Ans: The SUM function in DAX is used to sum up the values of a specific column in a table. For example, if you have a table called “Sales” with a column called “Revenue” you can use the SUM function to calculate the total revenue:

`= SUM(Sales[Revenue])`

The SUMX function in DAX is used to sum up the results of an expression evaluated for each row in a table or table expression. This means that you can use the SUMX function to perform a calculation for each row in a table, and then sum up the results of those calculations. For example, if you have a table called “Sales” with columns called “Revenue” and “Units Sold” you can use the SUMX function to calculate the total revenue multiplied by the units sold:

`= SUMX(Sales, Sales[Revenue] * Sales[Units Sold])`

In this example, the expression `Sales[Revenue] * Sales[Units Sold] `is evaluated for each row in the “Sales” table, and then the results are summed up to give the total revenue multiplied by the units sold.

In summary, the main difference between the SUM and SUMX functions in DAX is that SUM calculates the sum of a specific column in a table, while SUMX calculates the sum of the results of an expression evaluated for each row in a table or table expression.

Q. How would you implement dynamic filtering in a DAX measure?

Ans:

To implement dynamic filtering in a DAX measure, you can use a combination of the FILTER function and the CALCULATE function.

Here’s a general outline of the steps you can follow:

Identify the column that you want to filter on dynamically. This column can be in the same table as the measure or in a related table.

Create a measure that calculates the value you want to filter dynamically. This measure can use any DAX formula that is appropriate for your scenario.

Use the FILTER function to filter the column based on the dynamic measure. For example, if you have a measure called “Sales Amount” and you want to filter the “Product” column based on this measure, you can use the following formula:

`Filtered Sales Amount = CALCULATE([Sales Amount], FILTER(Products, [Sales Amount] > 1000))`

In this formula, the FILTER function is used to filter the “Products” table based on the dynamic measure “[Sales Amount] > 1000”. This means that only products with a sales amount greater than 1000 will be included in the calculation of the “Filtered Sales Amount” measure.

Use the dynamic measure in your calculation. For example, if you want to calculate the average sales amount for the filtered products, you can use the following formula:

`Average Filtered Sales Amount = AVERAGEX(FILTER(Products, [Sales Amount] > 1000), [Sales Amount])`

In this formula, the FILTER function is used to filter the “Products” table based on the dynamic measure “[Sales Amount] > 1000”. The AVERAGEX function is then used to calculate the average of the “Sales Amount” column for the filtered products.

Note that the specific implementation of dynamic filtering will depend on your specific scenario and data model. However, the general approach of using the FILTER function and a dynamic measure should be applicable in many cases.

Q. Can you explain the difference between the ALL and FILTER functions in DAX?

Ans:

The ALL function in DAX is used to remove all filters from a specified table or column. This means that if you use the ALL function on a table or column, any filters applied to that table or column will be removed, and the original context of the data will be restored.

For example, suppose you have a table called “Sales” with columns “Product”, “Region”, and “Sales Amount”. If you use the following formula:

`Sales Amount (No Filters) = CALCULATE(SUM(Sales[Sales Amount]), ALL(Sales))`

The ALL(Sales) function will remove any filters on the “Sales” table, and the `SUM(Sales[Sales Amount])` function will sum up the “Sales Amount” column across all products and regions.

The FILTER function in DAX is used to apply filters to a table or column based on a specified condition. This means that if you use the FILTER function on a table or column, only rows that meet the specified condition will be included in the calculation.

For example, suppose you have the same “Sales” table as before, and you want to calculate the sum of the “Sales Amount” column for products in the “North” region. If you use the following formula:

`North Sales Amount = CALCULATE(SUM(Sales[Sales Amount]), FILTER(Sales, Sales[Region] = "North"))`

The `FILTER(Sales, Sales[Region] = "North")` function will apply a filter to the “Sales” table, only including rows where the “Region” column equals “North”. The `SUM(Sales[Sales Amount])` function will then sum up the “Sales Amount” column for these rows.

In summary, the main difference between the ALL and FILTER functions in DAX is that ALL is used to remove filters, while FILTER is used to apply filters based on a specified condition.

Q: What is the role of the EVALUATE function in DAX?

EVALUATE is a DAX statement that is needed to execute a query. EVALUATE followed by any table expression returns the result of the table expression.

The EVALUATE function in DAX is used to evaluate a table expression and return the result as a table. It is commonly used in DAX queries to retrieve data from a data model or to perform calculations on a specific set of data.

The general syntax of the EVALUATE function is: `Evaluate <Expression of Table>`

The table expression can be any valid DAX expression that returns a table. For example, you could use the following DAX expression to retrieve all the rows from the “Sales” table: `EVALUATE Sales`

Or you could use the following DAX expression to retrieve the total sales amount for each product:

`EVALUATESUMMARIZECOLUMNS ('Products'[Product Name],"Total Sales Amount", SUM('Sales'[Sales Amount]))`

In this example, the SUMMARIZECOLUMNS function is used to group the data by product name and calculate the total sales amount for each product. The result is then returned as a table by the EVALUATE function.

The result of the EVALUATE function can be displayed in various ways depending on the tool you are using to work with DAX. For example, in Power BI Desktop or Excel, the result is displayed in a table visualization. In DAX Studio or SQL Server Management Studio (SSMS), the result is displayed in a grid view.

In summary, the EVALUATE function in DAX is a powerful tool for retrieving data from a data model or performing calculations on a specific set of data. It allows you to create custom tables and perform complex queries using DAX expressions.

Q. How do you create a dynamic hierarchy in DAX?

Ans: In DAX, you can create a dynamic hierarchy using a combination of functions and expressions. The following is a general approach that you can use to create a dynamic hierarchy in DAX:

Start by creating a base table that contains all the columns you want to use in your hierarchy. For example, you might have a table called “Sales” that contains columns like “Product”, “Category”, “Subcategory”, and “Date”.

Use the GENERATE function to create a table that lists all the unique combinations of values for each level of the hierarchy. For example, you might use the following DAX expression to create a table that lists all the unique combinations of product, category, and subcategory:

`HierarchyTable =GENERATE (DISTINCT ( Sales[Product] ),ADDCOLUMNS (DISTINCT ( Sales[Category] ),"Product", Sales[Product]),ADDCOLUMNS (DISTINCT ( Sales[Subcategory] ),"Product", Sales[Product],"Category", Sales[Category]))`

This expression generates a table with three columns: “Product”, “Category”, and “Subcategory”. Each row represents a unique combination of values for each level of the hierarchy.

Use the ROW function to create a column that concatenates the values of all levels of the hierarchy into a single string. For example, you might use the following DAX expression to create a column called “Hierarchy”:

`HierarchyTable[Hierarchy] =CONCATENATEX (ROW ( "Product", "Category", "Subcategory" ),IF (ISBLANK ( EARLIER ( HierarchyTable[Subcategory] ) ),"",CONCATENATE (EARLIER ( HierarchyTable[Product] ), " - ",EARLIER ( HierarchyTable[Category] ), " - ",EARLIER ( HierarchyTable[Subcategory] )))," | ")`

This expression concatenates the values of all levels of the hierarchy into a single string, separated by a delimiter (” | “). The IF function checks for blank values at the subcategory level, and skips that level if it is blank.

Use the HIERARCHY function to create a dynamic hierarchy using the “Hierarchy” column you created in step 3. For example, you might use the following DAX expression to create a dynamic hierarchy called “Product Hierarchy”:

`Product Hierarchy =HIERARCHY (HierarchyTable[Hierarchy]," | ","Product","Product Category","Product Subcategory")`

This expression creates a dynamic hierarchy using the “Hierarchy” column from the “HierarchyTable” table. The delimiter used in step 3 (” | “) is specified as the separator argument. The column names for each level of the hierarchy are also specified (“Product”, “Product Category”, “Product Subcategory”).

Once you have created a dynamic hierarchy, you can use it in your DAX expressions to aggregate data by different levels of the hierarchy, and to drill up or down the hierarchy as needed.