Microsoft Certified Data Analyst Associate Microsoft DA-100 5 — Questions and Answers
Question 1: You need to show the percentage contribution of each product to total sales, regardless of what is selected in a slicer. Which DAX pattern achieves this?
- Sales / CALCULATE(Sales, ALL(Products)) (Correct answer)
- Sales / TOTALYTD(Sales, Dates[Date])
- DIVIDE(Sales, SUMX(Products, Sales))
- Sales / CALCULATE(Sales, ALLSELECTED(Products))
Correct answer: Sales / CALCULATE(Sales, ALL(Products))
Dividing by CALCULATE(Sales, ALL(Products)) removes the product filter, returning sales for all products as the denominator regardless of slicer selection.
Question 2: Which Power Query step type is responsible for combining rows from two tables with identical column structures?
- Merge queries
- Append queries (Correct answer)
- Join queries
- Union transform
Correct answer: Append queries
Append queries stacks rows from two or more tables with the same columns, equivalent to a SQL UNION ALL operation.
Question 3: In DAX, what is the correct syntax to create a calculated column that returns the sales amount from a related table?
- LOOKUPVALUE([Amount], Sales[ProductID], Products[ProductID])
- RELATED(Sales[Amount]) (Correct answer)
- VALUES(Sales[Amount])
- FILTER(Sales, Sales[ProductID] = Products[ProductID])
Correct answer: RELATED(Sales[Amount])
RELATED traverses an existing many-to-one relationship to retrieve a scalar value from a related table's column.
Question 4: A Power BI report is embedded in a custom web application for external customers who do not have Power BI licenses. Which embedding approach is required?
- Embed for your organization (user owns data)
- Publish to web (public embed)
- Embed for your customers (app owns data) using a service principal (Correct answer)
- Power BI mobile embed SDK
Correct answer: Embed for your customers (app owns data) using a service principal
'Embed for your customers' uses a service principal or master account to authenticate on behalf of users who have no Power BI license, requiring Power BI Embedded (Azure) capacity.
Question 5: Which Power BI Desktop feature lets you write Python or R scripts to transform data in Power Query?
- Custom connector
- Script transform step (Run Python/R Script in Power Query) (Correct answer)
- External tool integration
- AI Insights
Correct answer: Script transform step (Run Python/R Script in Power Query)
Power Query supports 'Run Python Script' and 'Run R Script' transform steps that pass the current table to a script and return the result as a new table.
Question 6: In Power BI Service, which feature enables you to set alerts that notify you when a KPI value crosses a threshold on a dashboard tile?
- Data alerts (Correct answer)
- Conditional formatting rules
- Anomaly detection
- Metric scorecards
Correct answer: Data alerts
Data alerts in Power BI Service let you set threshold-based notifications on dashboard tiles for card and gauge visuals.
Question 7: When should you use a 'Live Connection' to Power BI Premium or Azure Analysis Services instead of importing data?
- When the dataset is under 1 GB and has simple transformations
- When a centralized semantic model must be shared across many reports without duplication (Correct answer)
- When you need full DAX authoring freedom in the downstream report
- When the data source does not support DirectQuery
Correct answer: When a centralized semantic model must be shared across many reports without duplication
Live Connection is ideal when a governed, centralized dataset (semantic model) already exists and multiple reports should consume it without each creating a separate copy.
Microsoft Certified Data Analyst Associate Microsoft DA-100 5
You need to show the percentage contribution of each product to total sales, regardless of what is selected in a slicer.
Which DAX pattern achieves this?