Excel Magic Trick 1269


Without Power Pivot: DAX Measures, Relationships & Data Model
Using Excel 2016, learn how to access the Data Model, build Relationships between tables and create DAX Measures (Formulas in Pivot Tables) without having PowerPivot Installed and without having to buy the upper level sku for Excel:
Microsoft: “All SKUs (on Win32) should allow analysis. if you need a dedicated, professional, tool for “self service BI”, PowerPivot is available in premium SKUs of Excel.”

(00:11) Problem introductions: Learn how to build a report from three tables without VLOOKUP or helper column formulas by accessing the Relationship feature on the Data Tab and utilizing the Data Model and DAX Measures (Formulas in Pivot Tables) indirectly through the Create PivotTable dialog box. This process will not allow us to open the “Manage Data Model window” to look at our Data Model because we do not have PowerPivot installed. For advanced Analytics and advanced features and easier to use user-interface, it is bet to install and use PowerPivot.
(01:08) Convert Proper Data Sets to Excel Tables using Ctrl + T (Excel Table Feature)
(01:38) Add first Table to the Data Model using the Create PivotTable dialog box and the “Add this to the Data Model” check box. The Data Model contains a Columnar Database that can hold millions of records.
(02:36) Look at PivotTable Field List and learn about the “ALL” tab which allows use to see all Excel Tables in the Excel Workbook and the fact that Excel Tables that have been added to the Data Model have a Dark Line above the table name in the PivotTable Field List to indicate that it has already been added to the Data Model.
(02:52) Create Relationships between the Tables using the Manage Relationships dialog box from the Data Tools group in the Data Ribbon Tab. When you create Relationships between tables, the Tables are automatically added to the Data Model.
(05:19) Create DAX Measure (DAX Formulas, DAX Measures, Calculated Columns, PivotTable Formulas are all synonyms) using the PivotTable Field List. From the PivotTable Field List, right-click the table and point to “Add Measure”. This required that you are familiar with the DAX Formula language. See the DAX Functions: SUMX function, RELATED function, and ROUND function. Learn how to create a DAX Formula that will calculate Net Revenue given Units, a Price that is “looked up”, and a discount. See that Number Formatting can be added directly to the DAX formula and that it will follow the Measure (formula) around into whichever PivotTable you drag it into.
(08:57) Drag new DAX Measure (Calculated Field) from the PivotTable Field List to the PivotTable Values area.
(09:35) Summary.


Be the first to comment

Leave a Reply