Microsoft Certified Data Analyst Associate Microsoft DA-100 4 — Questions and Answers
Question 1: In Power BI, Row-Level Security (RLS) is defined using which language?
- M (Power Query formula language)
- DAX filter expressions (Correct answer)
- SQL WHERE clauses
- Python predicates
Correct answer: DAX filter expressions
RLS roles are defined with DAX filter expressions that restrict which rows a given role can see in each table.
Question 2: What does 'query folding' mean in Power Query?
- Collapsing multiple query steps into one for readability
- Power Query translating M transformations into native data source queries (e.g., SQL) (Correct answer)
- Caching query results locally on the client machine
- Merging two queries into a single output table
Correct answer: Power Query translating M transformations into native data source queries (e.g., SQL)
Query folding occurs when Power Query pushes transformation logic back to the data source as a native query, reducing data transfer and improving performance.
Question 3: A report needs a measure that resets the running total at the start of each fiscal year. Which DAX pattern is most appropriate?
- Use TOTALYTD with a custom fiscal year-end date parameter
- Use CALCULATE with DATESYTD and specify the year-end date string (Correct answer)
- Use RUNNING SUM window function
- Use CUMULATE() from DAX 2.0
Correct answer: Use CALCULATE with DATESYTD and specify the year-end date string
CALCULATE combined with DATESYTD('<date column>', '<year-end-date>') produces a year-to-date running total that resets at the specified fiscal year end.
Question 4: Which Power BI Service role allows a user to publish reports to a workspace but not change workspace settings or add members?
- Viewer
- Contributor (Correct answer)
- Member
- Admin
Correct answer: Contributor
The Contributor role can add, edit, and delete content (including publishing reports) in a workspace but cannot manage workspace access or settings.
Question 5: In Power BI, what is a 'composite model'?
- A dataset that combines multiple Power BI files into one report
- A dataset that mixes Import, DirectQuery, and/or Dual storage modes in the same model (Correct answer)
- A report that embeds another report using a page embed URL
- A dataflow that references multiple data sources simultaneously
Correct answer: A dataset that mixes Import, DirectQuery, and/or Dual storage modes in the same model
A composite model allows tables within a single Power BI dataset to use different storage modes (Import, DirectQuery, or Dual), combining the benefits of each.
Question 6: Which DAX function allows you to ignore all existing filters on a column and return all values?
- REMOVEFILTERS
- ALL (Correct answer)
- ALLEXCEPT
- CROSSFILTER
Correct answer: ALL
ALL removes all filters from the specified table or column, returning the full set of values regardless of the current filter context.
Question 7: A Power BI dataset refresh fails because credentials for the data source have expired. Where in Power BI Service do you update them?
- Report Settings > Parameters
- Workspace Settings > Connections
- Dataset Settings > Data source credentials (Correct answer)
- Admin Portal > Data source management
Correct answer: Dataset Settings > Data source credentials
In Power BI Service, navigate to the dataset's Settings page and update credentials under the 'Data source credentials' section.
Microsoft Certified Data Analyst Associate Microsoft DA-100 4
In Power BI, Row-Level Security (RLS) is defined using which language?