DAX Query View was created some time ago and it was a great tool to run DAX inside Power BI environment.
However, testing a measure in DAX Query View may not be something so obvious. While the measure in a report obeys to the filter context and most of times results in a single value, the filter context doesn’t exist in Query View and the execution needs to return a table.
In this way, a simple copy/past of the measure in DAX Query View will not run. The measure needs to be adapted to be tested.
Let’s analyze one sample case to discover how this happens.
The Model
This image below illustrates the model. The image mostly speaks by itself. In our example, the challenge will be to retrieve the field CountryRegion from the table Address according to the context of SalesOrderHeader
The Measure
Our measure, called country, will use the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Country = VAR CurrentCustomerId = SELECTEDVALUE('SalesOrderHeader'[CustomerID]) VAR CurrentContry = CALCULATE( MAX('Address'[CountryRegion]), FILTER( 'CustomerAddress', 'CustomerAddress'[CustomerID] = CurrentCustomerId ) ) RETURN CurrentContry |
The image below shows one small example of the usage of this measure.
Testing the Measure in DAX Query View: SELECTEDVALUE
Let’s first test the SELECTEDVALUE function to check what it’s returning.
SELECTEDVALUE depends on the context of the report and this context will not be available in the DAX Query View.
We can simulate this context using the CALCULATE function. We will need to filter the information according to one specific values, one SalesOrderId, for example.
The first part of the expression can be like this:
|
1 |
CALCULATE(SELECTEDVALUE('SalesOrderHeader'[CustomerID]),'SalesOrderHeader'[SalesOrderId]="71856") |
The next step is transforming this single value in a table. The results in Query View need to be in a table format.
We can do this by using the ROW function. The result will be like this:
|
1 2 |
ROW("SelectedCustomer", CALCULATE(SELECTEDVALUE('SalesOrderHeader'[CustomerID]),'SalesOrderHeader'[SalesOrderId]=71856)) |
“SelectedCustomer” is the name of a field, the single field of this table result.
In this way, we confirmed the CustomerID value for the SalesOrderId 71856
Testing the expression for this CustomerId
We can change the rest of the expression to use this CustomerId in a fixed way, for testing purposes, to ensure the result this specific CustomerId provides.
The first part, the SELECTEDVALUE, can be removed. We will also need to change the result to be in a table format using the ROW function.
The expression will be like this:
|
1 2 3 4 5 6 7 8 9 10 11 |
VAR CurrentContry = CALCULATE( MAX('Address'[CountryRegion]), FILTER( 'CustomerAddress', 'CustomerAddress'[CustomerID] = 30033 ) ) RETURN ROW("CurrentCountry",CurrentContry) |
The full expression in DAX Query View</h2>
We can join the two parts. The only difference is that we shouldn’t use ROW for the SELECTEDVALUE, only for the final result.
This is how the expression will be:
Summary
In this way, we adapted a measure piece by piece, to execute tests in DAX Query View. By doing so we could identify which piece of the expression were causing trouble.
Load comments