There is a now a DAX query view in Power BI and let's get you started querying your data with whatever formula you need defined in the DAX query to get the analysis you are looking for!
This is the second post in my DAX query series. To get started querying, see my first post on DAX queries at https://www.datazoe.blog/post/dax-query-101-get-and-analyze-data-with-evaluate-filter-selectcolumns-summarize-summarizecolumn.
The most powerful part of a DAX query to me is being able to add whatever DAX formula I want to it. DAX formulas are defined in the semantic model with measures, calculated columns, and calculated tables which can then be used by report creators. In a DAX query I can do whatever DAX formula I want, even if it's not given to me by the model author.
Report visuals all use DAX queries to get data to visualize, and this ability to do additional DAX formulas in a DAX query is how you can add the limited set of aggregations on a data column without a measure. I covered this in detail in the first blog.
This visual is aggregating or summing the Sales data column (the summation symbol there indicates you can aggregate it in a visual and the column is a numeric data type).
The DAX query for this visual shows the DAX formula to sum the column.
Visual DAX queries have extra structure around them to handle being changed to different visuals, so the simplified version that I may do myself is just line 3 with an EVALUATE, and optionally the ORDER BY.
Let's re-create this simple DAX query to show Sales by Product again.
EVALUATE
SUMMARIZECOLUMNS(
financials[Product],
"Sales", sum(financials[Sales])
)
ORDER BY [Sales] DESCIf I wanted to also see Sales divided by 1000, I could write it out like this:
I can also have as many EVALUATEs as I want, so I could show these by Segment too in a 2nd result set.
We run into the same issue as we do with visuals when we constantly use implicit measures (DAX formulas inline), when we only aggregating in the visual. I have to copy and paste the visual to keep using it without setting up again. Just like I copied this DAX query. This is why we define measures in models, so they can be re-used easily. In visuals it's also required when they need to do something other than simple aggregations.
Thankfully there is a way to define a named measure in a DAX query too!
DEFINE is another keyword for DAX queries that is optional. This allows me to DEFINE MEASURE in a DAX query, that is define a named DAX formula in the scope of this DAX query. Let's try it out.
DEFINE
MEASURE 'financials'[Sum of Sales] = SUM(financials[Sales])
MEASURE 'financials'[Sales per 1000] = DIVIDE([Sum of Sales], 1000)
EVALUATE
SUMMARIZECOLUMNS(
financials[Product],
"Sales", [Sum of Sales],
"Sales per 1000", [Sales per 1000]
)
ORDER BY [Sales] DESC
EVALUATE
SUMMARIZECOLUMNS(
financials[Segment],
"Sales", [Sum of Sales],
"Sales per 1000", [Sales per 1000]
)
ORDER BY [Sales] DESCNow this is pretty cool. I have them up there at top and I can not only use them in any EVALUATE afterwards, but I can also use a measure defined in the DEFINE block in another measure in the DEFINE block. If I have to change the base measure, Sum of Sales, it's very easy and the change would be consistently applied throughout.
There are some rules to know about the DEFINE block. There can be only one DEFINE block and it has to be before the first EVALUATE block. You can define as many things in the DEFINE block as you want and what you have defined there can be used in other things defined in that block. So, you can't do things like DEFINE ... EVALUATE ... DEFINE ... EVALUATE or DEFINE ... DEFINE ... EVALUATE.
The MEASURE keyword requires you to specify a location in the model for this measure to pretend to be for this DAX query. So, you need to specify a table in the model and a unique name for the measure.
What happens when you use a location of an existing measure? The DAX query will use the DAX query DAX formula and ignore the model measure DAX formula. This opens up a whole new way to edit measures in DAX query view.
In DAX query view, when I used the DEFINE block a superscript appeared offering to "Update model: Add new measure", and a button by Run, previously greyed out, is now available and offering to "Update model with changes (2)". 2 indicating the number of changes it would make, as I have two measures defined.
In DAX query view, I have edit access to this model. That is, I can add or edit model measures (or anything else). It can see that this measure defined in the DAX query doesn't actually exist in the model, so it's offering a path to add it to the model, if you want. Clicking on the superscript above a MEASURE will just add that measure to the model, and clicking the "Update model with changes" button will add them both.
Let's click "Update model with changes".
A confirm dialog appears, letting you know, that just like every other model update you make, you can't just click ctrl-Z to undo it. You would have to manually delete each one once you add it, just like adding any measure to the model. Click "Update model" to continue.
And now two new model measures are added to the Data pane!
The "Update model with changes" is greyed out again and the superscript is gone, indicating the measures in the DEFINE block are in sync with the model measures.
Let's look at what happens when they are out of sync.
I'm going to modify the Sum of Sales to be doubled and Run the query.
A few things happened:
A new superscript appeared above the measure whose DAX formula changed: "Update model: Overwrite measure"
The "Update model with changes" is available again
In the results the value for Sales column changed, and also the value for Sales per 1000 changed as it was also defined and referencing the defined Sum of Sales in this DAX query
Note: The model measure remains unchanged at this time. Visuals and other reports using the model measure will not have this change. This DAX query gives you a safe place to try out changes and test the impact to the model.
DAX query view can see you have this measure in both the DAX query and the model, so now they are out of sync it gives the option to overwrite the model measure with this new DAX formula. And hovering over the measure in a DAX query will tell you not only both DAX formulas, but which one is currently being used.
In a DAX query, the measures defined in the DEFINE block take precedence and the peek will show model measure DAX formula with the note that it's currently inactive.
Clicking "Update model with changes" or the superscript will again update the DAX formula bringing them back in sync, and the cycle continues.
This means I can now use a model measure in any DAX query, add it as a DAX query scoped measure using DEFINE MEASURE, make changes, Run the query to validate the results, and add it back to the model. And, in addition, if I have model measures that reference other model measures, I can do this all together in one screen (finally!) without bouncing around each measure using the DAX formula bar to edit only one at a time and then create visuals in a report page to test it out!
DAX query view includes some Quick queries in the context menu to help you with this flow.
On a measure in the Data pane, right-click on the measure and choose Quick queries >
Evaluate to give you the measure back in a SUMMARIZECOLUMNS() to get you started adding appropriate group by columns to see the measure by.
Define and evaluate to give you the measure in a SUMMARIZECOLUMNS() and add it in a DEFINE block of that DAX query to make edits or just see the DAX formula.
Define with references and evaluate to also define all measures referenced in this measure (and their reference measures) in the DEFINE block.
Define new measure to give you the syntax to get started creating a measure from scratch and the DAX formula provided as placeholder is always COUNTROWS('Table')
Define all measure in this model (or table) to , as expected from the name, give you a DEFINE block with all model measures in it and a SUMMARIZECOLUMNS() listing them all out. A big time-saver writing it out for you and gives you a way to quickly search across all your model measure DAX formulas.
These will add the generated DAX query to a new query tab and auto-run.
Finally, if you have a DAX query already that doesn't have a DEFINE block, but you are using a model measure, you can use code actions to add a DEFINE MEASURE for you.
Simply click on the measure and a little lightbulb appears. This will only happen when there is not already a DEFINE block.
Clicking on the lightbulb will show the options to Define or Define with references.
One finally point on the DEFINE MEASURE. You cannot rename a measure with it. In DAX query view, the Data pane is available, and you can rename a model measure by double-clicking it or using right-click Rename action. Changing the name in a DEFINE MEASURE will mean it's now a new measure.
Another reason to use DEFINE MEASURE is also calculation groups. If you are testing out a calculation group, it will only apply to DAX formulas in a measure, either model measures or DAX query measures.
And there you have it! DEFINE MEASURE gives you more ways to get the most out of DAX queries with your data in the Power BI semantic model.
Reach out to me on LinkedIn if you have any questions! And here are some links with more information.
I also did a Power BI quick tip video with the amazing Mike Carlo of PowerBI.tips covering DEFINE in DAX query view and DAX queries: