Excel 365 – Data Model (Power Pivot) Advanced

Goals

Creation of advanced measures in DAX. Using Excel’s Cube Functions. Creation of Dashboards based on the Data Model.

Prerequisite

Very good Excel knowledge, basic knowledge of the Data Model, Power Pivot and DAX.

Contents

Reminders

• Add data to the data model,

• Create relationships,

• Create DAX calculated columns,

• Create simple measures (SUM, DISTINCTCOUNT),

• Use CALCULATE to change the filter context within a formula,

• Use Time Intelligence functions (DateAdd, ParallelPeriod,…).

DAX

• Use ISFILTERED, HASONEFILTER and HASONEVALUE to change the totals and subtotals calculations,

• Use SUMX to avoid creating unnecessary calculated columns,

• Use ADDCOLUMNS, SUMMARIZE and AVERAGEX to calculate averages of sums,

• Use RANKX to calculate ranking,

• Use FILTER to filter “against” relationships.

Excel Dashboards

• Add a Key Performance Indicator to an existing measure,

• Convert a PivotTable to formulas to open graphing and formatting options,

• Excel’s cube functions (CubeMember, CubeValue, etc.),

• Create a dashboard.