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

[AntrikshSharma]

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.

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

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

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.

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.

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

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!

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.

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.

Pretty useful, right!?

Thank you,

Antriksh Sharma

[Anonymous]

How do you then add this to your powerbi report?

[AntrikshSharma]

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

[Anonymous]

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.

[Anonymous]

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

[AntrikshSharma]

@Anonymous 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/