Excel Magic Trick 1435

DAX Query to Export Data from Data Model to Excel Sheet & Much More!

DESCRIPTION

See how to query the Data Model with DAX Code to Export Data to an Excel Sheet:
(00:14) Introduction, including looking at Data Model that downloadable file contains
(02:13) Use Existing Connections in Data Ribbon Tab to import data from Data Model into an Excel Sheet (Worksheet). See Edit DAX feature to pull data from Data Model into an Excel Sheet
(03:34) Use DAX Studio to create DAX Query
(04:42) EVALUATE Command
(05:07) Zoom feature in DAX Studio
(05:18) Expand Tables in Data Model to expose Columns
(05:38) CALCULATETABLE DAX Function to query a table based on three conditions, one each from three different dimension (lookup) tables, in order to return a subset table of a larger table. CALCULATETABLE is a function that can create a table based on a set of conditions or criteria from one or more tables.
(07:53) ADDCOLUMNS DAX Function to add columns to the output from the CALCULATETABLE Function for Retail Price and Standard Cost using the RELATED Function. ADDCOLUMNS is an iterative function that will iterate over a table and create an extra column.
(08:45) RELATED DAX Function to look up a price and cost for each product in each record of the table.
(10:19) Create an OR Logical Test in the CALCULATETABLE Function using the Or Operator Double Vertical BAR | |.
(12:01) Use DAX Formatter in DAX Studio to format DAX Query Code so it can be read more easily.
(13:04) Copy code from DAX Studio and paste into Edit DAX Dialog box in Excel in order to Query the Data Model and return a subset table to our Excel Sheet (Worksheet).
(13:48) Add Helper Column to DAX Query Table (Excel Table) to calculate Revenue
(15:07) Update and edit DAX Query Code in Edit DAX Dialog box to add a new column to the DAX Query Table (Excel Table) using a second RELATED Function to get the Standard Cost column.
(16:43) Unlink Table from Data Model.
(17:09) Summary

WORKBOOKS

Download







Be the first to comment

Leave a Reply