{"id":104011,"date":"2024-09-25T23:42:42","date_gmt":"2024-09-25T23:42:42","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=104011"},"modified":"2024-09-25T23:42:43","modified_gmt":"2024-09-25T23:42:43","slug":"debugging-dax-with-dax-query-view","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/debugging-dax-with-dax-query-view\/","title":{"rendered":"Debugging DAX with DAX Query View"},"content":{"rendered":"<p>DAX Query View was created some time ago and it was a great tool to run DAX inside Power BI environment.<\/p>\n<p>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&#8217;t exist in Query View and the execution needs to return a table.<\/p>\n<p>In this way, a simple copy\/past of the measure in DAX Query View will not run. The measure needs to be adapted to\u00a0be tested.<\/p>\n<p>Let&#8217;s analyze one sample case to discover how this happens.<\/p>\n<h2>The Model<\/h2>\n<p>This image below illustrates the model. The image mostly speaks by itself. In our example, the challenge will be to retrieve the field <strong>CountryRegion<\/strong> from the table Address according to the context of <strong>SalesOrderHeader<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1107\" height=\"720\" class=\"wp-image-104012\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/09\/a-screenshot-of-a-computer-description-automatica-4.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<h2>The Measure<\/h2>\n<p>Our measure, called country, will use the following code:<\/p>\n<pre class=\"lang:tsql decode:true \">Country =\nVAR CurrentCustomerId = SELECTEDVALUE('SalesOrderHeader'[CustomerID])\n\nVAR CurrentContry =\nCALCULATE(\nMAX('Address'[CountryRegion]),\n     FILTER(\n       'CustomerAddress',\n       'CustomerAddress'[CustomerID] = CurrentCustomerId\n      )\n)\n\nRETURN\nCurrentContry<\/pre>\n<p><span style=\"font-size: 1rem\">The image below shows one small example of the usage of this measure.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1280\" height=\"298\" class=\"wp-image-104013\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/09\/a-screenshot-of-a-computer-description-automatica-5.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<h2>Testing the Measure in DAX Query View: SELECTEDVALUE<\/h2>\n<p>Let&#8217;s first test the <strong>SELECTEDVALUE<\/strong> function to check what it&#8217;s returning.<\/p>\n<p><strong>SELECTEDVALUE<\/strong> depends on the context of the report and this context will not be available in the DAX Query View.<\/p>\n<p>We can simulate this context using the <strong>CALCULATE<\/strong> function. We will need to filter the information according to one specific values, one SalesOrderId, for example.<\/p>\n<p>The first part of the expression can be like this:<\/p>\n<pre class=\"lang:tsql decode:true \">CALCULATE(SELECTEDVALUE('SalesOrderHeader'[CustomerID]),'SalesOrderHeader'[SalesOrderId]=\"71856\")<\/pre>\n<p>The next step is transforming this single value in a table. The results in Query View need to be in a table format.<\/p>\n<p>We can do this by using the ROW function. The result will be like this:<\/p>\n<pre class=\"lang:tsql decode:true \">ROW(\"SelectedCustomer\",\nCALCULATE(SELECTEDVALUE('SalesOrderHeader'[CustomerID]),'SalesOrderHeader'[SalesOrderId]=71856))<\/pre>\n<p><span style=\"font-size: 1rem\">&#8220;SelectedCustomer&#8221; is the name of a field, the single field of this table result.<\/span><\/p>\n<p>In this way, we confirmed the CustomerID value for the SalesOrderId 71856<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"740\" height=\"322\" class=\"wp-image-104014\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/09\/a-screenshot-of-a-computer-description-automatica-6.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<h2>Testing the expression for this CustomerId<\/h2>\n<p>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.<\/p>\n<p>The first part, the <strong>SELECTEDVALUE, <\/strong>can be removed. We will also need to change the result to be in a table format using the <strong>ROW<\/strong> function.<\/p>\n<p>The expression will be like this:<\/p>\n<pre class=\"lang:tsql decode:true \">VAR CurrentContry =\nCALCULATE(\n    MAX('Address'[CountryRegion]),\n        FILTER(\n        'CustomerAddress',\n        'CustomerAddress'[CustomerID] = 30033\n     )\n)\n\nRETURN\nROW(\"CurrentCountry\",CurrentContry)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"416\" height=\"356\" class=\"wp-image-104015\" style=\"font-size: 1rem\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/09\/a-screenshot-of-a-computer-description-automatica-7.png\" alt=\"A screenshot of a computer\n\nDescription automatically generated\" \/><\/p>\n<h2>The full expression in DAX Query View&lt;\/h2&gt;<\/h2>\n<p>We can join the two parts. The only difference is that we shouldn&#8217;t use <strong>ROW<\/strong> for the <strong>SELECTEDVALUE<\/strong>, only for the final result.<\/p>\n<p>This is how the expression will be:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"897\" height=\"459\" class=\"wp-image-104016\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/09\/a-screenshot-of-a-computer-program-description-au.png\" alt=\"A screenshot of a computer program\n\nDescription automatically generated\" \/><\/p>\n<h2>Summary<\/h2>\n<p>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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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,&#8230;&hellip;<\/p>\n","protected":false},"author":50808,"featured_media":104017,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,159160,159166],"tags":[5872,159196,101611],"coauthors":[6810],"class_list":["post-104011","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blogs","category-business-intelligence","category-powerbi","tag-dax","tag-dax-query-view","tag-power-bi"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104011","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=104011"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104011\/revisions"}],"predecessor-version":[{"id":104018,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104011\/revisions\/104018"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/104017"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=104011"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=104011"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=104011"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=104011"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}