Power BI: Advanced
Course Outline	PBI005IW.pdf
PowerPoint Slides
Day	Modules	Sharing
1	Course Introduction Overview of Power BI Data Model Data Modelling in the Real World Dataset for these exercises Data Analysis Expressions (DAX) – Introduction Introduction to DAX Learning DAX Advice when creating DAX Third party tools to assist Measures Table Create a Table for Measures DAX – Measures and Filters Filters on a Measure Create simple aggregate measures Using measures on a report page Applying visual layouts and slicers/filters Initial Filter Context/Original Filter Context/Query Context DAX – The CALCULATE Function Introduction to the CALCULATE function Modifying filters with CALCULATE Add, replace, clear column filters Limitation of CALCULTE Adding Filters Replacing Filters The total row Removing/Clearing Filters The ALL and similar functions DAX – Evaluation Contexts Overview of evaluation contexts in DAX The Filter Context The Row Context Initial Filter (Query) context Initial Row context Seeing Row context Introducing a row context DAX – The Rules of Evaluation Introduction to the rules of evaluation The five steps Starting set of filters Specify filtering Filters are applied Propagation of filters Evaluation of expression Stepping through the rules in an example DAX – Contexts & Relationships Introduction to Relationships Filter context follows relationships Explore the filter context How filter context follows relationships Filter context against relationships The Both cross-filter direction Modify the filter direction The CROSSFILTER function Impact on totals DAX – The FILTER Function Introduction to the FILTER function FILTER returning a table Create a table with FILTER Modify existing filter context with both FILTER and CALCULATE Difference between these methods Using ALL function in FILTER Identical CALCULATE and FILTER Extended exercise – ALL functions DAX – Variables Overview of variables in DAX Creating and using variables Use variables to store a constant Use variables to avoid repetition Where to from here
2	Row Level Security Overview of RLS in Power BI Create dynamic RLS using USERNAME or USERPRINCIPALNAME Load data and enable RLS Testing dynamic RLS in Desktop RLS data access according to organisation hierarchy DAX parent-child functions Publish and assign roles in Power BI Service Create App Workspace Report Features Introduction to advanced features Overview of Drill-through Create report page with drillthrough filters Interacting with drill-through Overview of custom report tooltips Modify standard tooltip display Create a custom tooltip Apply the tooltip to a field Apply the tooltip to a visual Overview of Grouping and Binning Create a group Create a bin Binning a date field Date Dimension Overview of Date Dimension in BI Power BI Desktop settings Download or Create own table Data Table with a DateKey Convert a DateKey to a Date Create date table in Power Query Extended exercise - Use parameter to specify date table start and end Create date table with DAX Using CALENDARAUTO in DAX Extended Topic A – API Data Sources Overview of Web and API data sources Query an API data source HTTP and JSON terminology Transform a JSON response Publish to Power BI Service and setup refresh schedule Extended Topic B – Streaming Data Sources Overview of real time streaming data sources Push Datasets and Streaming Datasets Pushing data into a dataset Query data from PubNub stream Configure Dashboard tile to show streaming data from PubNub Create a REST API to push data into Power BI Create JSON object to insert data via this API Push data to created API using HTTP Post Create Dashboard tile to show updating data Extended Topic C – Files in Folders Overview of using a folder data source Query a folder containing multiple source files Column consistency Specify a template file to define columns View created queries / functions and dependencies Understand the created process of querying the folder Extended exercise – introduce additional files and unwanted files Additional Study (Extended Topic D) –Power Query M Formula Language Overview of Power Query M Formula Language Basics of Power Query M Values, Records, Lists Operators Conditional Testing Error handling Accessing table data More advanced Power Query M Each Expression Refer to other queries Understand a web query Remove Rows by Filter Unpivot & Rename Extended Topic B – Query Parameters (Dynamic Web Query) Importing a web query using parameters Creating a custom function Defining a List to create a new Query Invoking custom functions Using Error checking processes Cleaning queries Create measures and visualise queried data Extended Topic C – Group By Using the Group By feature Data Normalisation Appendix The Normal Forms 1st Normal Form

Recommanded Books
	Title	Artificial Intelligence with Microsoft Power BI: Simpler AI for the Enterprise
	ISBN	978-1-098-11275-2
	Author	Jen Stirrup, Thomas Weinandy
	Year	2024
	Publisher	O'Reilly Media
	Title	Effective Strategy Execution: Business Intelligence Using Microsoft Power BI (Management for Professionals)
	ISBN	978-3-662-68806-9
	Author	Bernd Heesen
	Year	2024
	Publisher	Springer
	Title	Power BI Machine Learning and OpenAI: Explore data through business intelligence, predictive analytics, and text generation
	ISBN	978-1-837-63615-0
	Author	Greg Beaumont
	Year	2023
	Publisher	Packt Publishing
	Title	Pro Power BI Architecture: Development, Deployment, Sharing, and Security for Microsoft Power BI Solutions
	ISBN	978-1-484-29537-3
	Author	Reza Rad
	Year	2023
	Publisher	Apress
	Title	Microsoft Power BI Performance Best Practices: A comprehensive guide to building consistently fast Power BI solutions
	ISBN	978-1-801-07644-9
	Author	B. Merchant, C. Webb
	Year	2022
	Publisher	Packt Publishing
	Title	Introducing Charticulator for Power BI: Design Vibrant and Customized Visual Representations of Data
	ISBN	978-1-484-28075-1
	Author	A. Box
	Year	2022
	Publisher	Apress
	Title	Extreme DAX: Take your Power BI and Microsoft data analytics skills to the next level
	ISBN	978-1-801-07851-1
	Author	Michiel Rozema, Henk Vlootman
	Year	2022
	Publisher	Packt Publishing
	Title	Mastering Power BI: Build Business Intelligence Applications Powered with DAX Calculations, Insightful Visualizations, Advanced BI Techniques, and Loads of Data Sources
	ISBN	978-9-391-03072-8
	Author	Chandraish Sinha
	Year	2021
	Publisher	BPB Publications
	Title	The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel
	ISBN	978-1-509-30697-8
	Author	Marco Russo, Alberto Ferrari
	Year	2019
	Publisher	Microsoft Press
	Title	Supercharge Power BI: Power BI Is Better When You Learn to Write DAX
	ISBN	978-1-615-47069-3
	Author	Matt Allington
	Year	2018
	Publisher	Holy Macro! Books
	Title	Power BI: Moving Beyond the Basics of Power BI and Learning about DAX Language
	ISBN	B089XYQCVR
	Author	Mike Morris, Nick Gallagher, et al.
	Year	2020
	Publisher	Albano Publishing LLC
	Title	DAX Cookbook: Over 120 recipes to enhance your business with analytics, reporting, and business intelligence
	ISBN	978-1-839-21707-4
	Author	Greg Deckler
	Year	2020
	Publisher	Packt Publishing
	Title	Hands-On Business Intelligence with DAX
	ISBN	978-1-838-82863-9
	Author	Ian Horne
	Year	2020
	Publisher	Packt
	Title	Applied DAX with Power BI: From zero to hero with 15-minute lessons
	ISBN	978-1-733-04610-7
	Author	Lachev, Teo
	Year	2019
	Publisher	Prologika Press
	Title	DAX Patterns
	ISBN	978-1-735-36520-6
	Author	Russo, Marco; Ferrari, Alberto
	Year	2020
	Publisher	SQLBI Corp.
	Title	Supercharge Excel: When you learn to Write DAX for Power Pivot
	ISBN	978-1-615-47053-2
	Author	Matt Allington
	Year	2018
	Publisher	Holy Macro! Books
	Title	Pro DAX with Power BI: Business Intelligence with Powerpivot and SQL Server Analysis Services Tabular
	ISBN	978-1-484-24896-6
	Author	Philip Seamark; Tom Martens
	Year	2019
	Publisher	Apress
	Title	Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence
	ISBN	978-1-484-23476-1
	Author	Philip Seamark
	Year	2018
	Publisher	Apress
	Title	Data Mashup with Microsoft Excel Using Power Query and M: Finding, Transforming, and Loading Data from External Sources
	ISBN	978-1-484-26017-3
	Author	Adam Aspin
	Year	2020
	Publisher	Apress
	Title	M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query
	ISBN	978-1-615-47034-1
	Author	Ken Puls, Miguel Escobar
	Year	2015
	Publisher	Ken Puls, Miguel Escobar

---