DA-100 Study Guide

 Hi All, 

These are some tips for clearing DA-100 exam. Please note, its NOT a beginner exam but needs intermediate skills in Power BI. You should at least 6 months to 1 year before you sit for this exam

1. Introduction to DA-100 Exam

• Successor to previously exam 70-788 called Analysing and Visualizing Data with Microsoft Power BI. 
• Must have skills on good understanding of how to use Power BI to perform data analysis
  

2.  Difference between 70-778 vs DA-100

•  70-788 retired on January 31, 2021 https://docs.microsoft.com/en-us/learn/certifications/exams/70-778
  
  
•  DA-100 is role based i.e. you become associate and expert into specific Microsoft skillset.
  
  
• https://query.prod.cms.rt.microsoft.com/cms/api/am/binary/RE2PjDI
  
  
• For Example: to become Microsoft Enterprise Expert, you need to clear MS-100, MS-101 and one of the Security Exam MS-500
  
  
• For Power BI, you need to clear DA-100 (associate) , and then clear DP-200, DP-201   (Expert) exam
  
  
• For Power BI, you need to clear DA-100 (associate) , and then clear DP-200, DP-201   (Expert) exam. Now, these DP-200 and DP-201 are getting replaced DP-203 (Beta )
  
  
• Around to 80% 70-778’s topics are covered in DA-100. Good News :) 
• DA-100 is more focused on AI concepts and newer features in Power BI Desktop which has released in last 2 years. 

3. Microsoft Learn for DA-100 Resources 

• One the best resource is to use DA-100 Microsoft Learn 
  
  
  
  
  
  
  
  

  Please note Microsoft keep on new topics in the exam very regularly. So when you write the exam, please check if your exam is getting being updated. 

The learning path for DA-100 is comprehensive and skillset are intermediate. 

4. DA-100 Online courses

a) BI Elite - USD 29 per month, 7 Days Trail 
The best course for DA-100. 

Created by Parker Stevens a Microsoft MVP in Data Platform. 
I used this course for a month. 

Its around 7-10 hours of video content. 

b) Udemy  - USD 16-17 (Depending on promotion) - With you for Life Long. No Subscription required.  

 25 hours. Covers Basic to Advanced. 

c) Measure up - USD 99   - 146 questions 

5. Main area to focus on study on  

  

A ) Prepare the Data (20-25%)

 

Get Data from Different Data Sources

1.       Identify and Connect to a Data Source

2.       Change Data Source Settings

3.       Select a Shared Dataset or Create a Local Dataset

4.       Select a Storage Mode. Concept of Storage Mode

5.       Use of CDS and workflows 

6.       Power Apps and Dataflow. Power BI Dataflows within Power BI Desktop. Directly Refresh via Power BI service.  

Profile the Data

7.       Identify Data Anomalies and use of scatter chart.

8.       Examine Data Structures

9.       Interrogate Column Properties and view data quality within Power BI.

10.   Interrogate Data Statistics and view the quality profile.

Clean, Transform, and Load the Data

11.   Resolve Inconsistencies, Unexpected or Null Values, and Data Quality Issues. Concept of replace values and remove error.

12.   Apply User-Friendly Value Replacements. Concept of replace values with null within something User-Friendly Value.

13.   Identify and Create Appropriate Keys for Joins. Concept of joins between tables. Adding custom column. Power BI functions such as Text.From.

14.    Evaluate and Transform Column Data Types. Concept of changing data types via Power Query.

15.   Apply Data Shape Transformations to Table Structures. Concept of transpose, reverse row, unpivot columns & group by.

16.   Combine Queries. Concept of merging, remove duplicates, & append queries. 

17.   Apply User-Friendly Naming Conventions to Columns and Queries. Concept of User-Friendly Naming Conventions and Queries why they are important.

18.   Leverage Advanced Editor to Modify Power Query M Code. Concept of Power Query M Code for more complex scenarios, use advanced editor, Power BI functions Table.TransformColumnsName(#”Renamed Columns”, Text.Upper or Text.Proper) .

19.   Configure Data Loading. Concepts of Enable load (to uncheck reduce the Power BI model size), Include in report refresh.  

20.   Resolve Data Import Errors. Concepts of Native Query under Transform Data, removing the calculated columns & incorrect data type.

 

B) Model the Data (25-30%)

Design a Data model

1.       Define the Tables. Show hide tables.

2.       Configure Table and Column Properties. Concepts of data modelling tab within Power BI, properties and configure synonyms, row label, storage mode (Import, Direct Query, Dual), data type and formatting options.

3.       Define Quick Measures. Quick measures allow you to write measures on the fly without writing any DAX. Right click on new quick measure.

4.       Flatten Out a Parent-Child Hierarchy. Use of Path function.

5.       Define Role-Playing Dimensions.

6.       Define a Relationship’s Cardinality and Cross-Filter Direction.  Concepts of 1-M, 1-1, M-1 relationships, Cross filter direction.

7.         Design the Data Model to Meet Performance Requirements. Concepts of Snowflake Schema and Star Schema (Single Fact table & other are around fact table)

8.       Resolve Many-to-Many Relationships.  Concepts of Many-to-Many relationships are convenient to set up, but they may impose performance and calculation issues. DAX distinct, union functions.

9.       Create a Common Date Table. Concepts of Blank query with Advanced editor with Date table M code.

10.   Define the Appropriate Level of Data Granularity.

Develop a Data Model

11.   Apply Cross-Filter Direction and Security Filtering

12.   Create Calculated Tables. Concepts of Calculated Tables with DAX, New Table with Filter, Summarize function 

13.   Create Hierarchies. 3 ways.

a.       Built in with date, File >> Options >> Current File >> Data Load >> Check Auto date/time under Time Intellisense.

b.       Drag multiple columns in Power BI visual (Axis). Not the preferred way.

c.       Drag fields on top of each of other. The preferred way as it can be reused.

14.   Create Calculated Columns. Concepts of Quantity and UnitPrice Columns within Sales table and need to compute multiplication of Quantity and UnitPrice. New Column with named as Total Price = Quantity * UnitPrice. Its row context as works on each row.

15.   Implement Row-Level Security Roles. Concepts of selective users should view only specific data and others can see entire dataset.

a.       Modelling >> Manage Roles.

b.       Role (Corporate Manager)
Tables (Custom Categories)
DAX expressions [CustomerCategoryName] = “Corporate”

c.       You can View As Corporate Manager

User can be logged in and in Power BI, under View as, you can check what that user can see by checking Other user.

d.       Publish >> Datasets >> Ellipsis >> Security >> Individual email OR Office 365 Groups.
In this way only end users can see what they are supposed to view.

16.   Set up the Q&A Feature. Concepts of Q&A Feature uses AI and machine learning. Configure Set up, Review Questions, Teach Q&A and supply synonyms, Manage Terms.

Create Measures by using DAX

 

17.   Use DAX to Build Complex Measures. Concepts of DAX that computes on the fly and use filter context. Calculated Column may increase size of data model. use all measures in one empty table. [ Measures ] =  { Blank() }

18.   Use CALCULATE to Manipulate Filters. Concepts of CALCULATE and its most important DAX function.

19.   Implement Time Intelligence Using DAX. Concepts of TOTALYTD, TOTALMTD

20.   Replace Numeric Columns with Measures. For more complex DAX, you can use Shift + Enter to view the DAX function clearly. 

21.   Use Basic Statistical Functions to Enhance Data. Concepts of MIN, MAX, AVERAGE, MEDIAN, STDED (Standard Deviation), VAR (Variance) 

22.   Create Semi-Additive Measures. Concepts of  Semi-Additive Measures with Calculate, Lastdate 

 

Optimize Model Performance

 

23.   Remove Unnecessary Rows and Columns. Concepts of filtering only data which is required. Use Remove duplicates, Remove blank rows, Remove errors, Remove Columns. 

24.   Identify Poorly Performing Measures, Relationships, and Visuals. Concepts of performance analyser. Add visuals and then click ok Start recording.  Option to export and stop the performance analyser.

25.   Improve Cardinality Levels by Changing Data Types. Concepts of Date time to Date which improve data model performance.

26.    Improve Cardinality Levels Through Summarization. Concepts of Group By and select the operations.

27.   Create and Manage Aggregations. Concepts of Reference (makes copy of table), Group By. manage aggregations. 

 C) Visualize the Data (20-25%)

 

Creating Reports

 

1.       Add Visualization Items to Reports. Concepts of visual items in the Visualization pane. Preview features are under Fine >> Options & settings >> Preview features. Insert tab has elements section (textbox, button, shapes, image), elements section (Power Platform), AI visual section (Q&A, Key influencers, Decomposition tree) 

2.       Choose an Appropriate Visualization Type. Concepts of requires some with experience, each chart serve gives different use cases.

3.       Format and Configure Visualizations. Concepts of formatting and Configure options, colouring, text, wrap, conditional formatting, search for a property such as size under formatting.

4.       Import a Custom Visual. Concepts of importing from AppSource, or local file in format of .pbviz file. Play visual. Format >> Edit Interactions

5.       Configure Conditional Formatting. Concepts of formatting >> Data colours >> Conditional Formatting. Minimum and Maximum values based on field. Or format by rules. 

6.       Apply Slicing and Filtering. Concepts of slicers, formatting, dropdown and list, filtering. Show slicer as hierarchy. 

7.       Add an R or Python Visual. Concepts of local installation of R or Python by going to Files >> Options >> Python OR R scripting. Drag R visual and drop columns. Write plot in R script editor. 

8.       Configure the Report Page. Concepts of use of charts, filters, Top N,

9.       Design and Configure for Accessibility. Concepts of background of Power BI page, shapes, lines, transparency of the visual, colour gradients, Tab order View >> Selection (Layer order, Tab order), Colour blind safe (View)

 

  Create Dashboards

 

10.   Set Mobile View. Concepts of publishing report to Power BI service, report to dashboard, pin report to dashboard. Dashboard >> Ellipsis >> Mobile View. Mobile layout. Ability to switch mobile view to desktop view.

11.   Manage Tiles on a Dashboard. Concepts of resizing tiles, move or rearrange, Q&A, add comment to a tile, tag people, open in focus mode to see dashboard tile full screen, export to .csv, Edit details.

12.     Configure Data Alerts. Concepts of receive alert emails when a metric goes above or below a desired threshold. Data available on tiles which are card, KPI and gauge visualization. Card visual >> Manage Alerts. Add alert rue, give a title, condition for a threshold of a sales goes below 1 million and email to be sent, alert configurations.

13.     Use the Q&A Feature. Concepts of Q&A in Dashboard. Q&A can display the user data in different visuals such as funnel chart, pie chart, map, scatter chart, stacked bar chart. Able to pin the Q&A visual.

14.   Add a Dashboard Theme. Concepts of dashboard theme. Ellipses >> Dashboard theme in Power BI service. Different options of theme such as Light, Dark, Colour-blind friendly or custom. Upload JSON theme for more control on visuals. 

15.   Pin a Live Report Page to a Dashboard. Concepts of entire report >> ellipses >> Pin a live page.  Ability to interactivity between visuals.

 

Enrich Reports for Usability

16.     Configure Bookmarks. Concepts of show and hide pages. Insert >> Button >> Blank button. Clear all filters. View >> Bookmarks. Add >> Clear all filters. Bookmarks save the state. Clear all filters button >> Visualizations >> Turn on action >> Type to Bookmark, Bookmark to Clear all filters.

17.   Create Custom Tooltips. Concepts of Page 1 >> Visualizations >> Page Information >> Tooltip. Page 2 Donut chart >> Formatting >> Tooltip >> Set Page to Page 1

18.   Edit and Configure Interactions Between Visuals. Concepts of Format >> Edit interactions. Select the visuals, change other visual if this has to disabled. 

19.   Configure Navigation for a Report. Concepts of tabs at the bottom of the Power BI report to switch between pages, but it’s a much better user experience if navigation occurs via buttons. Button >> Visualization >> Action >> Type & Bookmark.

20.   Apply Sorting. Concepts of visual sorting >> ellipsis  >> Sort By.
Add columns >> Examples from examples (for e.g. Day Order)
Data view >> Day name column >> Sort by >> Day Order
Report view >> day order works well.

21.   Configure Sync Slicers. Concepts of different pages should have same sliders across.
View >> click on Sync Sliders >> Check on their pages.

22.   Use the Selection Pane. Concepts of View >> Click on Selection >> can move layers  or tab order (show /hide).. Able to Group the layers   

23.   Use Drill-Through and Cross Filter. Concepts of cross filter select a Visual >> View >> Edit interactions >> can change from cross filtering instead of filtering. 
Table visual >> add multiple columns >> Under Visualizations >> Add drill-through fields here option >> add fields under Add drill-through fields >> Automatically adds back button.

24.    Drill Down into Data by Using Interactive Visuals.

25.   Export Report Data. Concepts of export a visual to CSV.  Export data as summarized data OR Underlying data.   

 

D) Analyze the Data (10-15%)

 

Enhance Reports to Expose Insights.

 

1.       Apply Conditional Formatting. Concepts of depends on field valid.

a.       Stacked Bar Chart >> Formatting >> Data Colours >> Ellipsis >> Format by Field Value (not colour  scale or Rules), >> Based on Field >> Select a measure >> Click OK.

Using DAX with IF statement value is low, then RED, else GREEN.

b.       You can’t Conditional Formatting for line chart. Convert bar chart to Line chart, you can get Conditional Formatting.

2.       Apply Slicers and Filters. Concepts of advanced slicers with custom visuals. Search for filter “Text Filter”, “Smart Filter by OKViz”, “Timeline slicer”.

a.       Drag Text filter and table visual. Add 2-3 fields in the table visual. Search for some text content on the Text Filter

b.       Drag Smart filter. It has autocompleted.

c.        Drag Timeline slicer. For Date fields.

3.       Perform Top N Analysis. Concepts of Top N within a visual.

4.       Explore Statistical Summary.

5.       Use the Q&A Visual. Concepts of  drag Q&A visual on Visualization pane. Review Q&A setup.

6.        Add a Quick Insights Result to a Report. Concepts of analysis option within a visual on Power BI Desktop. On Power BI Service >> Get Quick Insight.

7.       Create Reference Lines with the Analytics Pane.  Concepts of certain visual that have this option.  Line Chart >> format >> Trend Line, Constant Line, Min, Max, Average line, Forecast (Forecast length, Confidence interval)

8.        Use the Play Axis Feature of a Visualization. Concepts of certain visual only such as scattered chart.

Perform Advanced Analysis

 

1.       Identify Outliers. Concepts of Key Influencers Visual which is an AI driven insight visual. 

2.       Conduct Time Series Analysis. Concepts of change date time to time for this line chart visual.

3.       Use Groupings and Binning. Concepts of two different techniques to create new calculated partitions of your columns in the dataset. Select any columns from a table >> New Group.

4.       Use the Key Influencers to Explore Dimensional Variances. Concepts of convert bar chat to Key Influencers visual.

5.       Use the Decomposition Tree Visual to Break Down a Measure.

6.       Apply AI Insights. Concepts of 3 AI visuals within Power BI. Using Analyse option in Line Chart to either explain the decrease or increase.  

E) Deploy and Maintain Deliverables (10-15%) 

Manage Datasets

 

1.       Configure a Dataset Scheduled Refresh. Concepts of setting up a  scheduled refreshes on your dataset to make sure your data is always up to date. Power BI Pro licenses allow you to schedule up to 8 refreshes per day.

Power BI Premium allow you to schedule up to 48 times
Power BI Service >> Workspace >> Datasets >> Schedule Refresh.

2.       Configure Row-Level Security Group Membership. Concepts of Workspace >> Datasets + dataflows >> DA 100 Report >> Ellipses  >> Security >> Email addresses OR Office 365 Groups.

3.       Providing Access to Datasets. Concepts of providing access to a dataset directly to another member of your organization. Workspace >> Datasets + dataflows >> DA 100 Report >> Ellipses  >> Manage Permissions >> Add User >> aroh@contoso.com

In Power BI Desktop >> Get Data >> Power BI datasets >> DA 100 Report is accessible to aroh@contoso.com

Workspace >> Datasets + dataflows >> DA 100 Report >> Ellipses  >> View lineage. To view where sets is used.

4.       Configure Incremental Refresh Settings.  Concepts of incremental refresh allows you to only refresh a certain portion of the table. You can even specify to only refresh the table if Power BI detects that the data has changed.

5.       Endorse a Dataset. Concepts of endorse a dataset so that your users know which datasets are trusted to use for report development.

Workspace >> Datasets + dataflows >> DA 100 Report >> Ellipses >> Settings >> Endorsement with 3 options. Promote dataset and hit Apply.

In Power BI Desktop >> Get Data >> Power BI datasets >> DA 100 Report shows Promoted

Create and Manage Workspaces

 

6.       Create and Configure a Workspace. Concepts of creating a workspace >> Advanced to add specific users.

7.        Recommend a Development Lifecycle Strategy. Concepts of best practices concerning how to govern the development of your Power BI solutions.

8.       Assign Workspace Roles. Concepts of many workspace roles to choose from when deciding what role to assign to other users.

Workspace >> Access >> 4 different roles  (Admin, Member, Contributor, Viewer)

9.       Configure and Update a Workspace App. Concepts of Apps are a great way to package Power BI solutions and share them with other members of your organization or with the entire organization. In this video, we will publish our first app and then go through the process to update the app after changes are made to our Power BI items.

Create App >> Set up App Name >> Logo >> Navigation >> Permissions

10.    Publish, Import, and Update Assets in a Workspace. Concepts of to import Power BI reports into the Power BI Service how to update a report and dataset after it’s already been published to a workspace.

 All the best. 

--aaroh