Excel Magic Trick 1435: DAX Query to Export Data from Data Model to Excel Sheet & Much More!

Download File:
See how to query the Data Model with DAX Code to Export Data to an Excel Sheet:
1. (00:14) Introduction, including looking at Data Model that downloadable file contains
2. (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
3. (03:34) Use DAX Studio to create DAX Query
4. (04:42) EVALUATE Command
5. (05:07) Zoom feature in DAX Studio
6. (05:18) Expand Tables in Data Model to expose Columns
7. (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.
8. (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.
9. (08:45) REALTED DAX Function to look up a price and cost for each product in each record of the table.
10. (10:19) Create an OR Logical Test in the CALCULATETABLE Function using the Or Operator Double Vertical BAR | |.
11. (12:01) Use DAX Formatter in DAX Studio to format DAX Query Code so it can be read more easily.
12. (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. (13:48) Add Helper Column to DAX Query Table (Excel Table) to calculate Revenue
14. (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.
15. (16:43) Unlink Table from Data Model.
16. (17:09) Summary

[ElbinaRizakhanova]

Great explanation of DAX, watching these videos is an absolute pleasure

[oliverbuckley]

I didn’t know about the connection to the data model. It adds a whole new dimension to the data model functionality. Excellent insight. That DAX Studio also looks great. Excellent video. Thanks Mike.

[MiscaXL]

Absolutely amazing! Thank you for adding all these DAX tutorials: Excel has always been fun but now DAX is tons of fun as well!

[utalgreg]

Thanks Mike for all of your videos. To add the Revenue column without having to resize the table, right click the price column header, and the context menu will give you the option to insert one column to the left or one column to the right. Thanks

[johnywhy]

Excellent tutoring. You explain every click, which i need!

[cindyc]

This is very interesting video that have a lot of explanations. I need to watch a number of time to absolute more. DAX is fun to learn. Thanks.

[Soulenergy]

Thank you Sen Sie, this DAX learning thing is long path so far, happyli you make it less painful!

[daveportland]

Great video, outstanding job. The way you explain DAX is just crystal clear

[sunkannamadigonde]

I love your video series. Whenever it comes to my mind to learn any new excel feature, I always refer to your channel. One stop shopping for excel :)

[deepakagrawal]

Excellent video, Mike. In case, the user wants the entire dump of a table in Data Model in CSV, following R Script can be used inside Power BI Desktop:

write.csv(SDB, file="SDB.csv", row.names=FALSE)

This will save the entire table in a CSV file in the default R directory or you can give full path also viz.

R Script in Power BI Desktop is very powerful and very much intuitive to understand by Excel users. I just love it the most inside Power BI Desktop.

[NguyenNguyen-nrbx]

Absolutely phenomenal. Thank you so much Mike!

[deepakagrawal]

Mike. There is one way to shorten the code and that is by using the IN operator, however, it is not supported in Excel 2016. But, if you import this Excel Model into Power BI Desktop and then connect the same to DAX Studio, you can write the following DAX query which is much more easier to read and makes the query less complex in case there are multiple filter conditions:

EVALUATE
ADDCOLUMNS (
    CALCULATETABLE (
        fTransactions,
        dProduct[Product] IN {"Quad", "Carlota"},
        dCalendar[Year] = 2015,
        dCountry[CountyCode] = "USA"
    ),
    "Price", RELATED ( dProduct[RetailPrice] )
)


The IN operator in DAX supports multiple filter conditions also within the same table. For example, you can use the following DAX query to get the result only for May 2015 and June 2016:

EVALUATE
ADDCOLUMNS (
    CALCULATETABLE (
        fTransactions,
        dProduct[Product] IN { "Quad", "Carlota" },
        FILTER (
            ALL ( dCalendar[Year], dCalendar[Month] ),
            ( dCalendar[Year], dCalendar[Month] ) IN { ( 2015, "May" ), ( 2016, "June" ) }
        ),
        dCountry[CountyCode] = "USA"
    ),
    "Price", RELATED ( dProduct[RetailPrice] ),
    "Month", RELATED ( dCalendar[Month] ),
    "Year", RELATED ( dCalendar[Year] )
)


P.S. In the dCountry table, there is a typo in the column name. Instead of CountryCode it is CountyCode.

[mariamsaied]

Thank you so much. I always find an answer to my question in your tutorials.

[nishantkumar]

I was searching for this video for a long time.

[dafeac]

Great, It also would be great if you could spill a dimension out of a cubevalue formula

[bitechmacrobitechmacro]

Great video, again and again and again, as usual :))
This Dax Studio is super cool. Thank you so much for introducing us that tool. :))

[afedianine]

Awesome video, I just re-made your 1349 video using just this method, quicker!!

[darylellis]

Great video - I am hoping to do the same thing, but not seeing the same things as you at roughly the 2:20 mark. In your video - the existing connections dialog box opens up an dialog box with the options Connections and Tables. On mine - I see Connections and Queries. I am not able to select Table as you illustrated. I really like the ability to modify the DAX inside the table to include additional measures. I am not able to do that if I materialize a query. Do you know where/what I am doing different. Other than a newer version from 4 years ago? Thank you!

[cueva_mc]

Is it possible to export data from the data model to MS Access without having to export it to an excel sheet? or a way to export data from the data model to any other medium.?

[jaipopat]

Sir, Under what scenarios is CALCULATETABLE prefferred over FILTER ? When should you use CALCULATETABLE over FILTER.