MCQs on Mastering DAX for Analytics | Power BI
Unlock the power of DAX in Power BI! These 30 multiple-choice questions cover advanced DAX functions, time intelligence, and optimization techniques to help you excel in data analysis and modeling.
Advanced DAX Functions (FILTER, CALCULATE, RELATEDTABLE)
- Which of the following DAX functions is used to apply a filter to an expression in Power BI?
- a) CALCULATE
- b) FILTER
- c) RELATEDTABLE
- d) ALL
- What is the main purpose of the CALCULATE function in DAX?
- a) To modify the filter context of a calculation
- b) To return a single value from a table
- c) To filter data based on a condition
- d) To calculate aggregates
- Which of the following is a correct syntax for using the FILTER function in DAX?
- a) FILTER(table, condition)
- b) FILTER(column, condition)
- c) FILTER(table, column)
- d) FILTER(table, aggregate)
- How does the CALCULATE function interact with existing filter context?
- a) It removes all existing filters
- b) It ignores all filters
- c) It modifies or overrides the filter context
- d) It combines filters with OR logic
- What type of relationship does the RELATEDTABLE function rely on in DAX?
- a) One-to-one relationship
- b) One-to-many relationship
- c) Many-to-many relationship
- d) No relationship
- When using the FILTER function, which of the following is a valid logical operator for conditions?
- a) AND
- b) OR
- c) NOT
- d) All of the above
- What is returned by the RELATEDTABLE function in DAX?
- a) A table with the related rows
- b) A single value from a related table
- c) The primary key of the related table
- d) The foreign key column value
- Which of the following functions can be combined with CALCULATE to change the filter context in Power BI?
- a) FILTER
- b) ALL
- c) VALUES
- d) All of the above
- The FILTER function is typically used in conjunction with which type of calculation?
- a) Sum calculations
- b) Aggregated metrics with specific criteria
- c) Time intelligence calculations
- d) Text string manipulations
- Which function in DAX can be used to return a table of related values based on a foreign key?
- a) RELATED
- b) RELATEDTABLE
- c) LOOKUPVALUE
- d) EARLIER
Time Intelligence Functions
- What does the DAX function DATEADD do?
- a) Adds a specified number of intervals to a date
- b) Returns a date value for the first day of the month
- c) Calculates the difference between two dates
- d) Converts a text string into a date
- Which function is used to calculate the cumulative sum over a specified time period in Power BI?
- a) TOTALYTD
- b) DATESYTD
- c) SAMEPERIODLASTYEAR
- d) DATESINPERIOD
- What does the function SAMEPERIODLASTYEAR do in DAX?
- a) Returns the same period for the previous year based on the current context
- b) Computes the sum of values for the same period last year
- c) Returns all rows from the current year
- d) Computes year-over-year growth
- Which DAX function is used to return the last date of a given period?
- a) DATESINPERIOD
- b) LASTDATE
- c) FIRSTDATE
- d) NEXTDAY
- What is the purpose of the DAX function TOTALMTD?
- a) To calculate total sales by month
- b) To calculate the cumulative total for the current month
- c) To compute monthly averages
- d) To return month-over-month changes
- How does the DAX function DATEDIFF work?
- a) It calculates the difference between two dates in a specified unit (e.g., days, months)
- b) It returns the earliest date in a date range
- c) It adds or subtracts days from a date
- d) It compares two dates for equality
- Which of the following DAX functions can be used for calculating running totals?
- a) TOTALYTD
- b) DATESYTD
- c) DATESINPERIOD
- d) Both a and b
- What does the function PARALLELPERIOD return in DAX?
- a) Returns the sum for a specific period in the future
- b) Returns a table of values from a specified time period
- c) Returns the date that corresponds to the same period in a different year
- d) Returns the sum of sales for a given range
- Which of the following DAX functions is commonly used for month-to-date (MTD) calculations?
- a) SAMEPERIODLASTYEAR
- b) TOTALMTD
- c) DATESYTD
- d) DATESINPERIOD
- What is the role of the DAX function DATESINPERIOD?
- a) It calculates a rolling average over a period
- b) It filters a set of dates by a given period
- c) It returns dates within a specified range
- d) It adds months to a date value
Optimizing DAX Calculations
- Which of the following is a common technique for optimizing DAX query performance?
- a) Reducing the use of complex filters
- b) Using calculated columns instead of measures
- c) Avoiding the use of relationships
- d) Limiting the number of tables in the data model
- What is the purpose of the REMOVEFILTERS function in DAX?
- a) To clear all filter contexts
- b) To remove specific filters from a calculation
- c) To reset calculations to default values
- d) To remove null values from the result
- Which of the following practices can improve performance when writing DAX expressions?
- a) Reducing the number of DISTINCT and ALLSELECTED operations
- b) Using more complex formulas to reduce memory load
- c) Using calculated columns instead of measures
- d) Using fewer tables for DAX calculations
- Which DAX function helps to reduce the filter context for calculations?
- a) CALCULATE
- b) REMOVEFILTERS
- c) FILTER
- d) BOTH a and b
- How can the use of variables in DAX improve calculation performance?
- a) By storing intermediate results to avoid recalculating values multiple times
- b) By removing unnecessary relationships
- c) By reducing the complexity of formulas
- d) By changing the data model structure
- Which of the following is the primary method for optimizing performance in large datasets when using DAX?
- a) Using calculated tables
- b) Reducing row context in calculations
- c) Increasing memory usage
- d) Writing longer DAX formulas
- How does the SUMX function optimize calculations compared to the SUM function?
- a) It sums over an entire table and applies a filter
- b) It evaluates an expression for each row in a table
- c) It avoids aggregating data before calculations
- d) It uses an in-memory storage approach
- What effect does the use of the ALL function in DAX have on a calculation?
- a) It removes all filters from a column or table
- b) It keeps the filter context intact
- c) It performs a sum calculation over all rows
- d) It automatically aggregates data
- What type of DAX function is most likely to cause slow performance when used with large datasets?
- a) Iterative functions like SUMX
- b) Direct query functions
- c) Date-related functions
- d) Aggregated functions like COUNT
- Which DAX function can help reduce the impact of high cardinality on performance?
- a) VALUES
- b) DISTINCT
- c) ALL
- d) SUM
Answer Key
| Qno | Answer (Option with Text) |
|---|---|
| 1 | b) FILTER |
| 2 | a) To modify the filter context of a calculation |
| 3 | a) FILTER(table, condition) |
| 4 | c) It modifies or overrides the filter context |
| 5 | b) One-to-many relationship |
| 6 | d) All of the above |
| 7 | a) A table with the related rows |
| 8 | d) All of the above |
| 9 | b) Aggregated metrics with specific criteria |
| 10 | b) RELATEDTABLE |
| 11 | a) Adds a specified number of intervals to a date |
| 12 | a) TOTALYTD |
| 13 | a) Returns the same period for the previous year based on the current context |
| 14 | b) LASTDATE |
| 15 | b) To calculate the cumulative total for the current month |
| 16 | a) It calculates the difference between two dates in a specified unit |
| 17 | d) Both a and b |
| 18 | c) Returns the date that corresponds to the same period in a different year |
| 19 | b) TOTALMTD |
| 20 | b) It filters a set of dates by a given period |
| 21 | a) Reducing the use of complex filters |
| 22 | b) To remove specific filters from a calculation |
| 23 | a) Reducing the number of DISTINCT and ALLSELECTED operations |
| 24 | d) BOTH a and b |
| 25 | a) By storing intermediate results to avoid recalculating values multiple times |
| 26 | b) Reducing row context in calculations |
| 27 | b) It evaluates an expression for each row in a table |
| 28 | a) It removes all filters from a column or table |
| 29 | a) Iterative functions like SUMX |
| 30 | b) DISTINCT |