DAX Interview question in power bi for fresh applicant
Table of Contents
Toggle1. What is DAX?
DAX stands for Data Analysis Expressions. It is a formula language used in Power BI, Excel Power Pivot, and Analysis Services Tabular models for creating custom calculations and aggregations on data.
2. What are the key features of DAX?
The key features of DAX include calculation and aggregation capabilities, seamless integration with Microsoft tools, being a formula-based language similar to Excel, and support for tabulated data models.
3. What is a calculated column in DAX?
In DAX (Data Analysis Expressions), a calculated column is a column in a table that you create using a DAX formula. The formula defines how the values in each row of the calculated column are calculated based on values in other columns of the same row or from related tables. Unlike a regular (static) column, the values in a calculated column are not stored in the database; instead, they are computed on-the-fly based on the formula when queried.
4. What is a measure in DAX?
A measure is a calculation conducted in real time, generally during data visualization or reporting. Measures are specified using DAX expressions and are commonly used to calculate aggregations such as sum, average, and count,etc.
5. How do you define a calculated column in DAX?
To define a calculated column, you need to go to the “Modeling” tab in Power BI Desktop, select the table, and click on “New Column.” Then, you can enter the DAX formula that defines the calculation for the column.
6. How do you define a measure in DAX?
To define a measure, you need to go to the “Modeling” tab in Power BI Desktop, select the table, and click on “New Measure.” Then, you can enter the DAX formula that defines the calculation for the measure.
7. What is the difference between a calculated column and a measure in DAX?
The key difference is that calculated columns are computed during data loading and stored in the data model, while measures are calculated on the fly during data visualization. Calculated columns are useful for creating new columns based on existing data, while measures are used for aggregations and calculations.
Calculated Column:
- Purpose: Static values computed during data model refresh.
- Storage: Values stored in the data model.
- Context: Not context-sensitive.
- Example:
Commission= Sales[SalesAmount] * 0.1
Measure:
- Purpose: Dynamic aggregations computed on-the-fly during queries.
- Storage: Results not stored; calculated dynamically.
- Context: Context-aware, responds to user interactions.
- Example:
TotalSales = SUM(Sales[SalesAmount])
In short, calculated columns store static values, while measures provide dynamic, context-sensitive aggregations.
8. What is the difference between the CALCULATE and FILTER functions in DAX?
CALCULATE is used to modify the filter context of a calculation by applying additional filters or removing existing filters, allowing for more complex calculations. FILTER is used to apply filters to a table or column and returns a filtered table or column as a result.
CALCULATE:
- Purpose: Alters the context in which a formula is evaluated, applying filters and transformations.
- Usage: Wrap around a formula to modify its context.
- Example:
TotalSales = CALCULATE(SUM(Sales[SalesAmount]), Sales[Year] = 2022)
FILTER:
- Purpose: Applies a filter to a table or expression, restricting data based on specified conditions.
- Usage: Used within CALCULATE or directly in a formula to filter data.
- Example:
HighSales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[SalesAmount] > 1000))
In short, CALCULATE adjusts the evaluation context, while FILTER is used to set specific conditions for filtering data within that context.
9. How can you calculate the year-to-date sales using DAX?
To calculate the year-to-date (YTD) sales, you can use the following DAX formula:
DAX formula:
YTD Sales = TOTALYTD(SUM(‘Sales’[Sales]), ‘Date’[Date])
10. How do you calculate the total sales for a specific product using DAX?
To calculate the total sales for a specific product, you can use the following DAX formula:
Total Sales = CALCULATE(SUM(‘Sales’[Sales]), ‘Products’[ProductName] = “Bike”)
11. How do you use the IF function in DAX? Provide an example.
The IF function in DAX is used to perform conditional evaluations. It checks a given condition and returns different results based on whether the condition is true or false. Here’s an example:
Result = IF(‘Sales’[Quantity] > 10, “High”, “Low”)
12. Difference between the SUM and SUMX functions in DAX?
The SUM function in DAX is an aggregation function that computes the sum of a numeric expression for a certain column or table. It acts on a column and returns a single scalar result that represents the sum of all the values in that column. For example:
Total Sales = SUM(‘Sales’[SalesAmount])
whereas, the SUMX function is an iterator function that performs a computation expression to each row of a table before summarizing the results. It acts on a table and calculates each row individually, making it helpful for doing computations over numerous tables or applying complex expressions to each row before aggregation.
Total Sales = SUMX(‘Sales’, ‘Sales’[Quantity] * ‘Sales’[Price])
13. Difference between the SUMMARIZE and ADDCOLUMNS functions in DAX?
The SUMMARIZE function creates a summary table by grouping data by one or more columns and aggregating values with provided expressions or functions. It is frequently used to generate aggregated findings and construct a new table summarizing the data. For example:
Summarize Table = SUMMARIZE(Orders, Orders[CustomerID], Orders[ProductID], “TotalSales”, SUM(Orders[Sales]))
whereas the ADDCOLUMNS function creates a new table by adding one or more calculated columns to an existing table. It enables you to add computed numbers or expressions as new columns depending on current ones. For example:
Add column Table = ADDCOLUMNS(Orders, “Profit”, Orders[Sales] — Orders[Cost])
14. How do you calculate the previous period’s sales using DAX?
To calculate past-period sales, use the PREVIOUSPERIOD function in DAX. Here’s an example.
Previous Period Sales = CALCULATE(SUM(‘Sales’[Sales]),PREVIOUSPERIOD(‘Date’[Date]))
15. How do you calculate the cumulative sales for each month using DAX?
To calculate the total revenue for each month, use the following DAX formula:
Cumulative Sales = CALCULATE(SUM(‘Sales’[Sales]), FILTER(ALL(‘Date’), ‘Date’[Date] <= EARLIER(‘Date’[Date])))
16. How do you calculate the number of days between two dates using DAX?
To compute the number of days between two dates, use the DATEDIFF function in DAX. Here’s an example.
Days Difference = DATEDIFF(‘Date’[StartDate], ‘Date’[EndDate], DAY)
17. How do you use the IF function in DAX? Provide an example.
Conditional assessments in DAX are carried out using the IF function. Depending on whether the condition is true or false, it verifies a given condition and gives a different answer. As an example, consider this:
Result = IF('Sales'[Quantity] > 10, "High", "Low") 18. How do you handle complex calculations involving multiple tables in DAX?
Use methods like CALCULATE, FILTER, and RELATED to manage complex calculations involving multiple databases. To carry out the required calculations, these functions let you work with the filter context, apply particular requirements or criteria, and get results from related tables.
19. What is the role of the EARLIER function in DAX?
In an iterating calculation, the EARLIER function is used to refer to a previous row context. It allows you to iterate through a table’s rows and retrieve a column’s value from previous row.
20. How can you calculate the year-to-date sales using DAX?
You can use the following DAX formula to get the year-to-date (YTD) sales:
YTD Sales = TOTALYTD(SUM(‘Sales’[Sales]), ‘Date’[Date])
21. How do you add or subtract a specific number of days from a date using DAX?
The DATEADD function in DAX can be used to add or remove days from a date. As an example, consider this:
Output = DATEADD(‘Date’[Date], 7, DAY)
22. How do you remove leading or trailing spaces from a text string using DAX?
To remove leading or trailing spaces from a text string, you can use the TRIM function in DAX. Here’s an example:
Trimmed String = TRIM(‘Orders’[Product])
23. How do you format a date value using DAX?
To format a date value in DAX, use the FORMAT function. Here’s an example.
Formatted Date = FORMAT(‘Date’[Date], “yyyy-mm-dd”)
This formula formats the ‘Date’ column using the “yyyy-mm-dd” format.
24. What is the purpose of the UNION function in Power BI?
UNION merges two tables with the same structure into a single table. For example:
CombinedTable = UNION('Table1', 'Table2') 25. What is the purpose of the RELATED function in DAX?
RELATED is used to get column values from a related table. For example:
ProductName = RELATED('Product'[ProductName]) 26. How do you handle errors in DAX?
Use the IFERROR function for correctly handling errors. For example:
SafeDivision = IFERROR('Table'[Numerator] / 'Table'[Denominator], 0) 27. What are query folding and how do they impact performance in Power BI?
Power BI’s query folding feature allows you to push operations back to the data source. It increases performance by allowing the source system to execute data transformation. Ensure that query folding is used to improve efficiency.
28. What is the purpose of the INTERSECT function in DAX?
INTERSECT is used to find the common values between two tables. For example:
CommonValues = INTERSECT('Table1', 'Table2') 29. Explain the use of the EARLIER and EARLIEST functions in DAX with an example?
EARLIER refers to a previous row context, whereas EARLIEST refers to the first row of a table. For example:
PreviousValue = EARLIER('Table'[Column]) + 1 30. How do you set up time intelligence functions in Power BI to analyze patterns over time?
Use DAX functions such as TOTALYTD, SAMEPERIODLASTYEAR, and DATESBETWEEN to do time-based calculations and comparisons.
31. What is the purpose of the SELECTEDVALUE function in DAX?
If the column holds only one value, SELECTEDVALUE returns that value; otherwise, it returns an error.
SelectedProductName = SELECTEDVALUE('Product'[Product]) 32. Explain the purpose of the KEEPFILTERS function in DAX and provide an example.
KEEPFILTERS retains current filters when assessing new expressions.
FilteredSales = KEEPFILTERS(Sales[Quantity] > 10)
33. What is the purpose of the DETAILROWS function in DAX, and how would you use it?
DETAILROWS makes a table containing all rows that contribute to a given outcome.
DetailedSales = DETAILROWS('Product'[Product]) 34. How can you use DAX to dynamically select the top N items based on user input?
TopNItems =
TOPN(
SELECTEDVALUE('Top N'[N]),
VALUES('Product'[Product]),
[Total Sales], DESC
)
35. What is the purpose of the UNICHAR function in DAX?
UNICHAR returns the Unicode character linked with the given code.
EmojiColumn = UNICHAR(128525)
36. What is the role of the PATHCONTAINS function in DAX?
PATHCONTAINS determines whether a column includes a specific path.
PathContainsCheck = PATHCONTAINS("Path1/Path2", 'Table'[HierarchyColumn]) 37. What is the purpose of the TREATAS function in DAX?
TREATAS applies filters from one table to another. It can be effective in creating virtual relationships.
38. Can you name a few DAX functions used for time intelligence, and provide an example?
TOTALYTD:
Function: Calculates the total year-to-date value for a given expression.
Example:
Total Sales YTD = TOTALYTD(SUM('Sales'[SalesAmount]), 'Date'[Date])
This would give you the total sales from the beginning of the year up to the specified date.
TOTALQTD:
Function: Calculates the total quarter-to-date value for a given expression.
Example:
Total Sales QTD = TOTALQTD(SUM('Sales'[SalesAmount]), 'Date'[Date])
TOTALMTD:
Function: Calculates the total month-to-date value for a given expression.
Example:<pre>
Total Sales MTD = TOTALMTD(SUM('Sales'[SalesAmount]), 'Date'[Date])
DATESBETWEEN:
Function: Returns a table that contains a column of all dates between two specified dates.
Example:
Sales Between Dates = CALCULATE(SUM('Sales'[SalesAmount]), DATESBETWEEN('Date'[Date], [Start Date], [End Date]))
/ 39. Describe the difference between DAX's CONCATENATE and CONCATENATEX functions?
CONCATENATE is used to concatenate text strings, whereas CONCATENATEX is an indexing function that concatenates column values using a delimiter. Example:
Concatenated List = CONCATENATEX('Products', 'Products'[Product Name], ", ") 40. How do you calculate the percentage of total in DAX?
To find a percentage of the total, apply the DIVIDE function. Example:
% of Total Sales = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL('Products'))) 41. In DAX, how do you create a calculated column that decides whether a value exists in another table?
Combine the RELATEDTABLE function with an IF statement. Example:
ExistsInAnotherTable = IF(RELATEDTABLE('OtherTable'[ID]) <> BLANK(), "Yes", "No") 42. How can you calculate the compound annual growth rate (CAGR) in DAX?
Using to XIRR function. Example:
CAGR = XIRR('Sales'[Amount], 'Sales'[Date]) 43. What is the difference between the COUNTROWS and COUNTAX functions in DAX?
COUNTROWS counts the number of rows in a table, whereas COUNTAX counts the number of rows in a table or its expression.
44. Explain the usage of the SWITCH function in DAX?
SWITCH is used to specify numerous conditional statements. Example:
CategoryType = SWITCH('Products'[Category], "A", "Type A", "B", "Type B", "Other") 45. How can you create a slicer in Power BI using DAX?
Make a disconnected table and use it as a slicer. Example:
SlicerTable = VALUES('Category'[Category]) 46. What is the purpose of the ALL function in DAX?
ALL removes every filter from a table or column. Example:
TotalSalesAll = CALCULATE(SUM('Sales'[Amount]), ALL('Products')) 47. How do you manage many conditions in DAX?
Use the && (AND) or || (OR) operators. Example:
HighValue = IF('Sales'[Amount] > 1000 && 'Sales'[Quantity] > 5, "High", "Low") 48. What is the purpose of the GENERATESERIES function in DAX?
GENERATESERIES generates a table containing a range of numbers. Example:
DateRange = GENERATESERIES(DATE(2022, 1, 1), DATE(2022, 12, 31), 1)
49. What is the purpose of the LOOKUPVALUE function in DAX?
LOOKUPVALUE returns a single value from a column that corresponds to the supplied search criteria.
50. How can you use DAX to calculate a moving average over a given time period?
Use the AVERAGEX function with a specific date range. Example:
MovingAvg = AVERAGEX(FILTER('Sales', 'Sales'[Date] >= TODAY() - 30), 'Sales'[Amount])