Microsoft Certified Data Analyst Associate Microsoft DA-100 3 — Questions and Answers
Question 1: Which DAX function should you use to create a disconnected slicer table that controls a measure without a direct relationship?
- RELATED
- SELECTEDVALUE (Correct answer)
- LOOKUPVALUE
- USERELATIONSHIP
Correct answer: SELECTEDVALUE
SELECTEDVALUE returns the single selected value from a column, enabling measures to respond to slicer selections from an unrelated table.
Question 2: In Power BI Service, what is the function of a 'dataflow'?
- Moves dashboards between workspaces
- Provides reusable, cloud-based ETL transformations stored as entities (Correct answer)
- Schedules report export to PDF
- Manages gateway connections for on-premises data
Correct answer: Provides reusable, cloud-based ETL transformations stored as entities
Dataflows are self-service data preparation tools in Power BI Service that create reusable Power Query transformations stored as Common Data Model entities.
Question 3: A user complains that a Power BI report is slow when slicing by date. The fact table has 50 million rows in Import mode. What is the FIRST optimization to try?
- Switch the date table to DirectQuery
- Ensure a separate date dimension table with a one-to-many relationship is used (Correct answer)
- Increase the dataset size limit in the workspace settings
- Add more columns to the fact table for pre-aggregation
Correct answer: Ensure a separate date dimension table with a one-to-many relationship is used
A proper date dimension table with a star-schema one-to-many relationship enables VertiPaq to filter the fact table efficiently using column-store compression.
Question 4: Which Power BI feature lets you define reusable Power Query steps that can be called like a function across multiple queries?
- Query folding
- Custom connector
- Power Query function (let…in) (Correct answer)
- Dataflow entity
Correct answer: Power Query function (let…in)
In Power Query, you can create a function using a let…in expression with parameters, then invoke it from other queries to avoid duplicating transformation logic.
Question 5: In DAX, what is the difference between CALCULATE and CALCULATETABLE?
- CALCULATE returns a scalar value; CALCULATETABLE returns a table (Correct answer)
- CALCULATE is used in measures; CALCULATETABLE is used in columns only
- CALCULATE supports row context; CALCULATETABLE supports filter context only
- They are identical; CALCULATETABLE is deprecated
Correct answer: CALCULATE returns a scalar value; CALCULATETABLE returns a table
CALCULATE evaluates an expression (returning a scalar) in a modified filter context, while CALCULATETABLE evaluates a table expression returning a virtual table.
Question 6: A Power BI report must show data from an on-premises SQL Server without importing data. Which connectivity option should be used?
- Import mode with scheduled refresh
- DirectQuery through an on-premises data gateway (Correct answer)
- Push dataset via REST API
- Live connection to Analysis Services
Correct answer: DirectQuery through an on-premises data gateway
DirectQuery with an on-premises data gateway sends queries directly to the SQL Server at report render time, requiring no data import.
Question 7: Which DAX function is used to iterate over all rows of a table and return a single aggregated value?
- FILTER
- SUMX (Correct answer)
- RELATED
- ALLSELECTED
Correct answer: SUMX
SUMX (and other 'X' functions like AVERAGEX, MAXX) iterates each row of a table, evaluates an expression per row, then aggregates the results.
Microsoft Certified Data Analyst Associate Microsoft DA-100 3
Which DAX function should you use to create a disconnected slicer table that controls a measure without a direct relationship?