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.
4. DA-100 Online courses
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
