Microsoft Certified Data Analyst Associate Analyzing Data with Microsoft Power BI 4 — Questions and Answers
Question 1: You have sales data with a 'Ship Date' and an 'Order Date'. You want to calculate metrics based on both dates. What model design approach should you use?
- Add two separate date tables and create inactive relationships to one
- Use a single date table with two relationships, making one active and one inactive (Correct answer)
- Merge both date columns into one column in Power Query
- Create two calculated columns that reference the single date table
Correct answer: Use a single date table with two relationships, making one active and one inactive
The standard pattern is one date table related to both date columns, with one relationship active and the other inactive, using USERELATIONSHIP in measures to switch context.
Question 2: Which DAX function returns the value from a previous row in a sorted table, commonly used for period-over-period comparisons within a table visual?
- EARLIER
- PREVIOUSMONTH
- LAG
- OFFSET (Correct answer)
Correct answer: OFFSET
OFFSET is a DAX window function (introduced in 2022) that returns a value from a row that is a specified number of positions before or after the current row in a sorted partition.
Question 3: A Power BI dataset has row-level security (RLS) configured. Who can see all rows regardless of RLS rules when accessing the dataset in Power BI service?
- Report viewers
- Dataset contributors
- Workspace Admins and Members (dataset owners) (Correct answer)
- Anyone with a Power BI Pro license
Correct answer: Workspace Admins and Members (dataset owners)
Workspace Admins and Members who own the dataset are not subject to RLS; only users assigned to RLS roles (typically Viewers) have row-level restrictions applied.
Question 4: What is the main advantage of using DirectQuery mode instead of Import mode in Power BI?
- Faster query performance due to in-memory storage
- Data is always current because queries go directly to the source (Correct answer)
- Supports all DAX functions without limitation
- Reduces load on the source database
Correct answer: Data is always current because queries go directly to the source
DirectQuery sends queries to the source database in real time, ensuring data is always up-to-date without requiring scheduled refreshes.
Question 5: Which Power BI feature would you use to let different users see the same report but with data automatically filtered to their own region?
- Personal bookmarks
- Dynamic row-level security with USERNAME() or USERPRINCIPALNAME() (Correct answer)
- Report-level filters
- Workspace-level permissions
Correct answer: Dynamic row-level security with USERNAME() or USERPRINCIPALNAME()
Dynamic RLS uses DAX functions like USERNAME() or USERPRINCIPALNAME() to compare the logged-in user against a security table and filter data accordingly.
Question 6: In Power BI, what does the 'Analyze in Excel' feature allow a user to do?
- Export the report as an Excel file with static data
- Connect Excel to the Power BI dataset using PivotTables and PivotCharts (Correct answer)
- Import an Excel file into Power BI automatically
- Schedule a data refresh from an Excel source
Correct answer: Connect Excel to the Power BI dataset using PivotTables and PivotCharts
Analyze in Excel creates a live connection from Excel to the Power BI dataset, allowing users to build PivotTables and PivotCharts against the same data model.
Question 7: What is the purpose of the ALLEXCEPT function in DAX?
- Removes all filters from the model except calculated columns
- Removes all filters from a table except those applied to specified columns (Correct answer)
- Applies filters to all tables except the one specified
- Returns all rows except blank values
Correct answer: Removes all filters from a table except those applied to specified columns
ALLEXCEPT removes all context filters from a table except for filters on the columns you explicitly list, useful for ratio calculations that need to preserve certain filter context.
Microsoft Certified Data Analyst Associate Analyzing Data with Microsoft Power BI 4
You have sales data with a 'Ship Date' and an 'Order Date'.
You want to calculate metrics based on both dates.
What model design approach should you use?