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 and this 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 Analysis Services Tabular.
First open DAX Studio from start or from 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 CALCUALTETABLE
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!
You can get creative and start using variables to make the code readable. The window support formatting using DAX Studio.
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?
Comments