Edit 30-08-2020 (E01): Edited/added few points after one of my colleagues gave this exam last week.
Early this year, Microsoft decided to retire their existing certification paths for their new Role-based Certifications. With this, our beloved Power BI certification also went through a transition i.e. from 70-778 to DA-100. DA-100 (beta) had been out for a while now but unfortunately, people living in the Indian Subcontinent were not allowed to appear (Not sure why!). So, we all waited and then waited for a little longer until finally (a few months later) it was released to the public in the first week of July 2020.
After waiting for 2+ weeks, due to a lack of appointments with Pearson on weekends, I finally managed to appear on 25th July 2020. This blog post is my attempt to write a brief review of this exam. I have divided the Power BI DA-100 exam review into 3 parts namely Exam Details, Exam Pattern and Exam Topics.
DA-100 Exam Details
- Exam Length: 180 mins
- Total Questions: 55 divided into 3 sections
- First Section:47 questions
- Second Section: 4 questions (all pertaining to a single case study)
- Third Section: 4 questions (all pertaining to a single case study)
I have categorized the exam into 3 sections for simplicity and ease of understanding. The exam starts with Section 1 comprising of 47 questions. You can answer them or skip a few and come back later to answer. Once you are done, you submit the section. Once you submit, you cannot go back to that section and modify answers. The same thing happens for Section 2 followed by Section 3 and finally post Section 3, the Power BI DA-100 exam ends.
Note: Some of the questions from Section 1 involve use-cases where once you submit, you are unable to go back and edit. The exam pops you up with a message for such questions. So be rest assured!
Edit 30-08-2020 (E01): The three sections can come in any order
- MCQs with one correct answer
- MCQs with two correct answers
- Arrange the steps
- Complete the DAX
- Say Yes or No
- Select from Dropdown
Multiple Choice Questions(MCQs) have one/two answers. You are informed in advance if a question has 1 or 2 correct answers and if you select 3, it doesn’t allow you. So you don’t have to overthink and do R&D there.
Arrange the steps questions are to understand if you know the steps involved in getting to the right solution. eg. If you are asked, “How do you assign a User an RLS Role? “. You are presented with 7-8 steps like “Open PBI Desktop and create Roles”, “Open PBI Service and create Roles”, “Publish your Report to PBI Service”, “Assign the role to user id”. You are supposed to pick and choose steps and order them to get the desired steps.
Complete the DAX question explains a scenario wherein you are expected to write a DAX expression. The answer will already have a DAX expression but with missing functions on one side and DAX functions to be used on the other side. You are expected to drag-drop the correct functions to complete it.
Say Yes or No questions are scenario-based. You are given a scenario and asked 3 Yes/No questions based on it.
Select from Dropdown questions are usually asked in pairs. You are given a scenario and asked 2 questions about it with answers to choose from the dropdown. The dropdown usually had 3 or 4 options.
The syllabus or skills measured PDF that Microsoft shared with us for Power BI DA-100 exam earlier is spot on. The weight-ages for the sections hold more or less true. I have attempted to use that as a guide and list down an approximate number of questions I remember against each. I have also mentioned a quick overview first (for people too lazy to read everything) and then a topic wise overview referring to that PDF.
Note: The total number of questions may not match as for a single question, you may have 2 questions in a Select from Dropdown question or 3 questions in a Say Yes or No question. The idea is to point out important topics.
Quick Overview (for people who wanna read less 😉😅)
- Language: The language of questions is tricky (at times ambiguous) but the questions themselves are not that tricky. Please read a question twice before marking your answer and you should be good.
- DAX Usage: You don’t have to be a DAX Ninja. Basic Functions are all that you need to know. DAX functions that I encountered: CALCULATE, FILTER, ALL, ALLSELECTED, SUM, SUMX, COUNT, COUNTX, IF, KEEPFILTERS, FORMAT, RELATED, RELATEDTABLE.
- M-Query Usage: There was only one direct M-Query code related question. So no need to know any M-Query at all. You should be comfortable with Query Editor though. A lot of questions related to various functions/buttons in Query Editor. Edit 30-08-2020 (E01): 2 questions on M-Query
- Get Data & Data Modelling: Get Data from Folders, Many-to-Many Data Modelling, active/inactive relationships and RLS.
- Visualizations: Visual related questions were on basic visuals, nothing on Custom/R/Python visuals. 3-4 questions from the Analytics pane. Tip: Know your Scatter Plot, Key Influencers and Bar Chart Visuals properly
- Power BI Service: Workspace Permissions, a bit of RLS and you are good
- Case Studies: Both Case studies are easy and you should definitely not be worried about it. Read slowly and you would ace them.
Prepare the Data (20-25%)
- Get data from different data sources
- 6 questions on getting files from a Folder and how you can tweak it in Power Query
- Nothing on shared datasets, Common Data Service (CDS) and query performance issues
- Profile the data
- 4 questions on profiling, please go through the Data Preview tab in the View Ribbon in Query Editor
- Also, read about the table.profile() function
- Clean, transform and load the data
- Lots of questions on Query Editor. The way I like to say it is, “Ensure that you have pressed every single button up there”.
- CLEAN vs TRIM, First Rows as Header vs Headers as First Rows, Merge vs Merge as New, Append vs Append as New these subtle things that one might miss.
- Nothing on diagnostics or optimization.
- Edit 30-08-2020 (E01):1 question on the concept of “Query Folding”
Model the Data (25-30%)
- Design a data model
- Ensure that you have played around a lot in the Model tab of PBI Desktop
- Lots of questions related to relationship’s cardinality and cross-filter direction and especially many-to-many relationships, ensure you do sufficient hand’s on with many-to-many and that you understand data granularity.
- A lot of questions on calendar tables and how you can use them with active/inactive relationships. Ensure you have a sound understanding of RELATED function.
- Nothing on flattening of parent-child hierarchy(PATH) or quick measures.
- Develop a data model
- Ensure you know a bit about synonyms and how to create a Measure Table
- 3 questions on hierarchies
- 6 questions on RLS in PBI Desktop and PBI Service combined
- Nothing about security filtering
- Create measures by using DAX
- You don’t have to be a DAX Ninja. There were 4 questions.
- Basic Functions are all that you need to know. DAX functions that I encountered: CALCULATE, FILTER, ALL, ALLSELECTED, SUM, SUMX, COUNT, COUNTX, IF, KEEPFILTERS, FORMAT, RELATED, RELATEDTABLE
- Time Intelligence functions that appeared were DATEADD and SAMEPERIODLASTYEAR
- 1 question on PERCENTILEX.INC vs PERCENTILEX.EXC
- Optimize model performance
- Nothing in particular about this topic and definitely nothing about Performance Analyzer
- There was a question about aggregation to reduce model size.
- In almost all questions related to Modeling, this line followed: “The solution should ensure that size of the dataset is minimal”
Visualize the Data (20-25%)
- Create reports
- 4 questions of visuals, to understand how well can you read the visual, to understand if you know how to create a visual is you are showing the output and so on.
- Questions on Scatter Plot, Key Influencers and Bar Chart Visuals. Do read about Clustering in Scatter Plot.
- Nothing related to Custom/R/Python visuals and accessibility. Edit 30-08-2020 (E01): 1 question on R visual
- Create dashboards
- 1 question on tile refresh
- 1 question on data classification/sensitivity labels
- Nothing on data alerts, Q&A, pinning or mobile view
- Enrich reports for usability
- 2 questions on sync slicers, page filters vs report filters
- 1 question on page navigation
- 1 question on edit and configure interactions between visuals
- Nothing on drilldown, drillthrough and bookmarks
- Edit 30-08-2020 (E01):1 question on mobile view
Analyze the Data (10-15%)
- Enhance reports to expose insights
- 2 questions on reference lines by using Analytics pane
- 1 question on top N analysis/filter
- Nothing on Q&A visual and Quick Insights
- Perform advanced analysis
- 1 question on forecast
- 1 question on Key Influencers
- Nothing on decomposition tree, AI insights, groupings and binnings or identifying outliers
Deploy and Maintain Deliverables (10-15%)
- Manage datasets
- As mentioned above, 6 questions on RLS of which 2/3 related to roles in service
- Nothing related to scheduled refresh, incremental refresh settings, endorse a dataset or providing access to datasets
- Create and manage workspaces
- 2/3 questions on assigning workspace roles
- 1 question on workspace app
- 1 question on recommending a development lifecycle strategy
If you are looking into Power BI and AWS RDS backed solutions, you would definitely find my latest post Connecting Power BI to AWS RDS PostgreSQL helpful. Do check it out.