In this tutorial, you’ll learn how to use the Query Builder feature to easily create queries and measures in DAX Studio.
You can access this by clicking on the Query Builder option found in the Home tab. This opens a new pane where you can specify columns, measures, and filters to create a query.
To test it out, let’s create a basic DAX query. Drag and drop a field from the Metadata pane to the Query Builder.
In this case, the columns are the Brand and Color. The table is filtered to only show brands which have “Red” as the color.
When you click the Run Query option at the bottom, the Results pane displays a table containing two rows: Brand and Color. You can also see that the Color column only contains “Red.”
Create A Basic Measure In Query Builder
To create a query measure using the Query Builder, click the New button. This creates a new measure and opens a new pane where you can write the DAX code.
You can also specify the table in your data model where you want to declare the new measure you created. In this case, it’s the Customers table.
Name your new measure. For the DAX code, let’s compute for the Total Sales using the SUMX function. It’s divided by 10,000 to reduce the data amount in the result.
When you click OK, you can see that the measure has been committed in the Query Builder. If you click the Run Query option, the Results pane will show a new column containing the Total Sales amount.
Create A Measure With Ranking In Query Builder
As another example, let’s create a second measure that ranks the color of each brand.
Remove the color filter. Create a new measure called Color Rank. The DAX code for this measure uses the RANKX function.
In the first argument, you need the CALCULATETABLE function to evaluate the product color VALUE modified by the REMOVEFILTERS function.
In the second argument, you need to call the Total Sales measure. The result is then sorted in descending order using the DESC function.
When you click OK, you can see that a new column has been added to the table. The Color Rank column ranks the color according to their Total Sales amount. This is done for each brand name.
You can also add filters to the measure. You can filter the color column and choose to only show colors that start with the letter B. For this to work, you need to change the REMOVEFILTERS function to ALLSELECTED.
When you run the edited query, you can see that the Color column only shows data that start with the letter B. The Color Rank column also changed how it ranked each row.
You can also use the Order By option in the Query Builder to sort the rows in your table.
The Edit Query Option
Another useful feature in Query Builder is the Edit Query option. This allows you to view the code automatically generated by the Query Builder.
For this example, this is what the code looks like:
You can see that it contains the same lines of code in your measures. However, to display the result in a table format, Query Builder uses the SUMMARIZECOLUMNS function over the Brand and Color column.
You can also see that the Order By option is translated into DAX code form.
The Auto Generate Query Option
If you edit your entries in the Query Builder and then click Run Query, you’ll notice that it won’t make any changes to the Query Builder’s built-in DAX code. The table in the Results pane also won’t be updated.
This is because every time you make changes, you need to click the Auto Generate Query option.
It functions similarly to a refresh button. This option ensures that each change you make in Query Builder gets reflected in the built-in DAX code and in the results.
***** Related Links *****
DAX Query Example Using Various Keywords & Functions
Format DAX Codes Using DAX Studio’s Special Features
DAX Query Optimization Techniques And Lessons
Conclusion
The Query Builder feature in DAX Studio offers an easy way to create queries and measures. This is especially helpful for users who are just starting to learn about Power BI and DAX Studio.
The great thing about the Query Builder is you don’t need to manually write the DAX code yourself. It will do it for you.
Enterprise DNA Experts
