cancel
Showing results for 
Search instead for 
Did you mean: 
AntrikshSharma

How to build queries using DAX Studio's user interface?

Since June 2020 a new feature has been introduced in DAX Studio https://daxstudio.org/ that allows you to design queries using the UI, earlier you would have to write code from scratch or import extract of Performance Analyzer from Power BI. Query Builder is a great add-on to this tool

In this blog I will show you how you can use the interface to create queries yourself.

To start with you either need a model inside PBI file, Excel’s PowerPivot or a model deployed in SQL Server Analysis Services (Tabular).

First open DAX Studio from start menu or from inside PBI/Power Pivot/SSAS and click on Query Builder.

1.png

Once you click it, it will open the Builder pane.

2.png

This newly opened pane allows you to drag and drop columns and In this pane you can also add and create new measures.

Once you drag columns or measure just click Run Query and Dax Studio will generate result in the result pane

3.png

If you are like me who likes to investigate the code generated behind the Queries then you can click on “Edit Query” option and it will present the code generated by Query builder.

4.png

Important Note, as of now this feature will only work for SSAS version supporting SUMMARIZECOLUMNS, versions including and prior to 2014 generate queries using CALCULATETABLE/ADDCOLUMNS/SUMMARIZE.

9.png

Moving on if you want to explore further then you can even specify Filters:

5.png

Creating Query Measures ( Local to the session )

If you want to create measures for testing purpose before deploying to the model then you can simply click on the New button and start writing your code, for this example, I am going to create a new measure for sales in 2007 or 2009.

1. To create a new local measure

2. Specify the home table for the measure

3. Change the measure name

4. Start daxing!

6.png

As you can see from the previous image it also has the Intellisense feature.

You can get creative and start using variables to make the code readable. The window also support formatting using DAX formatter.

7.png

Once done just click Ok and click on Run Query, you can click on edit query to check the code generated behind the query. To format the code press F6.

8.png

Pretty useful, right!?

 

Thank you,

Antriksh Sharma

Comments

How do you then add this to your powerbi report?

 

@StevenT Once you are convinced with the result, you can build measures and Calculated Tables.

Thanks AntrikshSharma.
I'm impressed with the clarity and detail of this post. Thanks for your help!
So you can than take the code and literally copy/paste it in a Report as a measure or a calculated table on the report?

I tried doing that with this query builder code and it failed. Then I realized you have to take out the Evaluate keyword when creating a new table with it.


 

One last question, do you have a separate blog or something similar regarding various PowerBI tips, tricks, etc.?

@StevenT Thanks! Yup excluding the DEFINE and EVALUATE part, they are used internally by the engine  so we don't need to specify them in Measures and Calculated tables in PBI.

 

Yup, sometimes I try to blog here https://www.antmanbi.com/