Common DAX Expressions and Scenarios Power BI Playbook
In this course we'll build an intelligent data model to facilitate sales analysis for an online clothing retailer with the DAX Language in Power BI Desktop. Completing this course will grant you the knowledge needed to create data objects with DAX.
What you'll learn
As a data professional, have you ever wanted to produce an array of data objects using the DAX language? In this course, Common DAX Expressions and Scenarios Power BI Playbook, you will learn foundation skills in dimensional modelling and programming with the DAX Language in Power BI Desktop. First, you will learn the basic design principles. Next, you will discover how to write DAX code to implement the basic model framework. Finally, you will explore the DAX language and use it to extend and enhance the model. When you have completed this course, you will have the skills and knowledge needed to make your data available via a robust and flexible self service analytical interface. Software required: Power BI Desktop.
Table of contents
- A Structured Approach to Power Bi Design 6m
- Building a Foundation 3m
- What Is a Dimensional Model? 2m
- What Does a Dimensional Model Do 6m
- The Dimensional Modelling Process 4m
- Exploring the Data 7m
- Designing the Data Model 3m
- Creating Dimension Tables with GROUPBY 6m
- Creating a Fact Table with SUMMARIZECOLUMNS 5m
- Creating Data Star with the Model Tool 5m
- Creating Text Columns for Data Analysis 1m
- Create Useful Columns for Analysis 4m
- Replace a String with SUBSTITUTE 7m
- Identify Text Positions with SEARCH & FIND 5m
- Cut Out Text Parts with LEFT MID & RIGHT 8m
- Data Cleansing with SUBSTITUTE 2m
- Lookup Values with RELATED and RELATEDTABLE 5m
- Create an Aggregated Column with RELATEDTABLE 5m
- Join Text Strings with COMBINEVALUES 3m
- Capitalizing Text with UPPER and REPLACE 3m
- Creating Text Columns Summary 1m
- Create a Year and Month Column with FORMAT 8m
- Create Last Date in Month Columns with EOMONTH 6m
- Create the First Day of the Month with DATE 3m
- Create the First Day in the Last and Next Month with DATEADD 5m
- Comparing American and European Options with WEEKNUM 5m
- Create Week Number Columns with WEEKNUM 5m
- Create the Last Date in a Week Column with WEEKDAY 4m
- Create the First Date in a Week Column with WEEKDAY 3m
- Using Calendar Columns in Data Analysis 5m
- Creating Measures with Aggregation Functions 4m
- Introducing Aggregation Functions 5m
- Visualizing Measures and the Filter Context 8m
- Counting Sales with SUM and COUNTROWS 2m
- Creating Measures with AVERAGE MEDIAN MIN MAX and DISTINCTCOUNT 3m
- Filter Context Deep Dive 5m
- Calculate the Last Sales Date Column with an Aggregation Function 4m
- Creating Groups Bands and Hierarchies with Conditional Values 5m
- Creating Conditional Statements with SWITCH TRUE and IN 6m
- Implement Business Logic with AND OR NOT 4m
- Create Conditional SWITCH Statements with AND OR NOT 8m
- Creating Bands and Sorting Columns 4m
- Introducing Expression Variables with VAR 3m
- Ranking Products with FILTER VAR and COUNTROWS 6m
- Creating Intelligent Measures with Iterating X Functions 4m
- Calculating Total product Sales with SUMX and RELATEDTABLE 6m
- Static Ranking with RANKX 3m
- Creating a Ranking Measure with RANKX and ALL 6m
- Dynamic Ranking with RANKX and ALLSELECTED 5m
- Creating a Mobile Sales Measure with SUMX and FILTER 6m
- Creating a Red Product Sales Measure with VAR FILTER RELATED and SUMX 1m
- Calculating Multi Item Sales with VAR FILTER and SUMX 1m
- Calculating First and Latest Dates with MINX and MAXX 1m
- Calculating the Sales Value Percent with ALL and DIVIDE 5m
- Taking Control of the Evaluation Context with CALCULATE 4m
- Comparing the Inner Workings of Two Expressions 3m
- Introducing Calculate and Context Transition 3m
- Context Transition in Action 3m
- Creating a Semi Additive Measure with CALCULATE 5m
- Three Ways to Filter Semi Additive Measures with CALCULATE and FILTER 7m
- Filtering Measures with Time Intelligence Functions 5m
- Cumulative Year Analysis with TOTALYD and SAMEPERIODLASTYEAR 5m
- Analyse Promotion Activity over Time with DATEADD 5m
- Analyse Promotion Activity by Nesting DATEADD 2m
- Calculate Monthly Percentage Change with PREVIOUSMONTH 4m
- Calculate Moving Annual Totals with DATESINPERIOD and LASTDATE 7m
- Create a Key Performance Indicator with DATESINPERIOD and LASTDATE 5m
- Create Complex Attributes for RFM Analysis 3m
- Calculating Seniority with CALCULATE DATEADD DATEDIFF MINX and MAXX 8m
- Calculate Recency with CALCULATE and DATEDIFF 6m
- Calculating Frequency with CALCULATE and DISTINCTCOUNT 4m
- Creating a Measure with a Compound Key 2m
- Calculating Filtered Averages 1m
- Creating Measures with Multiple Filters 2m
- Module and Course Summary 1m
About the author
Passionate about Business Intelligence and delivering knowledge and insight to support better decisions. An advocate of Self-Service Reporting and Analytics and a Data Visualisation evangelist. Andi has delivered face to face Business Intelligence training to many industries, covering topics such as Report Design, Semantic Layer, Dimensional modeling and Data Visualisation. Andi has led many Data Warehouse and Business Intelligence development projects and is well versed in taking complex an... more