Some more possible interview questions and answers about DAX in Power BI based on my experience and understanding.
In the previous blog, we have gone through some context and relationship-related questions and answers.
In this blog, we will go into aggregation related questions.
Please remember, only questions and answers can’t help you to clear any interview, you have to practice each and every syntax. Otherwise, it will be very difficult for you to explain any example using DAX.
Question 24: What is the purpose of summarization in any Data Analysis or BI project?
For any industry, millions of transaction data have been captured. From this large table, we can prepare a summary table using aggregation logic and display a summarised visual.
Performance of visual will be faster if data is fetching from the summary table instead of the detail table which has raw data. You will get more optimize reports for a better experience.
Question 25: Which functions are you going to use for summarization?
There are mainly 3 functions,
- GROUP BY
Question 26: Do you know what is difference between SUMMARIZE and SUMMARIZECOLUMNS?
- SUMMARIZE function has both the context filter and row. Whereas SUMMARIZECOLUMNS has one only filter context.
- In comparison with SUMMARIZE, the SUMMARIZECOLUMNS function produces an optimized query plan. It means it has to perform lesser steps when the engine run the queries.
Question 27: If you observe, GROUP BY and SUMMARIZE, both have the same syntax. Then what is the difference between them?
GROUP BY differs from SUMMARIZE and SUMMARIZECOLUMNS in the <expression> section arguments.
GROUP BY only works with DAX iterator functions, so it uses SUMX instead of SUM, AVERAGEX rather than AVERAGE. For specific scenarios, it works.
Before appearing to interview, practice some examples or scenarios to use the above functions. You can refer to my blog as your practice guideline.
Question 28: What is the difference between CALCULATETABLE and CALCULATE?
In terms of functionality, CALCULATETABLE is the same as CALCULATE function, but the difference is in their output.
CALCULATETABLE returns a table whereas CALCULATE returns a single value like an integer or a string.
Question 29: CALCULATETABLE and FILTER, both functions return a table, then what is the difference between them?
- Both have the same syntax and will generate the same number of rows.
- But there is a difference between the CALCULATETABLE and FILTER functions in terms of formula interpretation.
- CALCULATETABLE first work on filter context, then evaluate the expression.
- On the other hand, FILTER iterates the first expression results and it is not working on the filter context.
Practice some examples of these above functions. You can refer to my blog for your practice purpose.
Question 30: In spite of having a relationship between two tables, still we are using CROSSFILTER. Why is it so?
CROSSFILTER function is used to implement a specific cross-filtering behaviour in a calculation where a relationship exists between two columns.
For example, we have two tables Fact-Sales and Dim-Product. They are joined with product_key.
Now we create a table with year, sales and product count (considering products from product dimension). Here is the output
But, this is wrong, the product count is repeated for each year.
To solve this, if we are going to change the relationship between these two tables as both cross filter direction, then all other measures will be impacted. But we want to use both cross filter direction relationships only for this measure. We can use the CROSSFILTER function and recreate the measure.
CALCULATE(DISTINCTCOUNT(<ProductID from Dim-Product>,CROSSFILTER(<Sales_Fact-ProductID>,<Dim_Product_ProductID>Both))
Question 31: What is the purpose of the USERELATIONSHIP function?
The USERELATIONSHIP function does not return anything.
It specifies between two columns to define the relationship for a calculation. It uses as an argument for other functions like CALCULATE, CALCULATETABLE etc.
Question 32: What is the difference between RELATED and RELATEDTABLE?
- The RELATED function returns a column from another related table. It requires a relationship between the current table and the related table. It follows many to one relationship. Without mentioning a relationship, it will not work.
- The RELATEDTABLE returns a table of values. It considers an existing table as a parameter. It follows one to many relationships, which means you need to create the measure in that table that is holding one information.
To find some practice examples on USERELATIONSHIP, RELATED and RELATEDTABLE, please check my blog for your reference.
In this blog, we get some idea about more interview questions on DAX mainly aggregation and relationship functions. We will know more DAX interview questions in the next blog.
If you have any questions related to this project, please feel free to post your comments.
Please like, comment and subscribe to my YouTube channel. 🙂 Keep Learning.