Excel 2013 capability I didn’t know about – querying your data model with DAX

Did you know that you can write DAX queries against tables in your workbook’s data model in Excel 2013? You can, although I didn’t know you could until yesterday. I’m surprised I didn’t learn about this until now, This comes in real handy when experimenting with DAX. Here’s how to do it:

1. Import some data into a workbook data model

2. Create a new sheet

3. In the data tab of the ribbon click on Existing Connections and in the Existing Connections dialog, select the Tables tab:

ECD

 

4. Select the table you want to query and click the Open button

5. In the Import Data dialog, select Table and click OK:

ID

 

6. You now have a table in the worksheet that contains the contents of the table from the data model. To change that table into the result of a DAX query on your data model, right click on any cell in the table and select Table \ Edit DAX:

TED

 

7. In the Edit DAX dialog, change the Command Type dropdown from Table to DAX and enter your DAX query:

ED

 

8.The table now changes to the result of your DAX query. Pretty cool!

Result

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: