40180

DAX VAR defining named variables in the middle of the measure code

<h3>Question</h3>

I have been presented here with a bizarre exploitation of VAR function. So far I have encountered VAR in the beginning of the measure, right after the equal sign. See the code below:

Expected Result = SUMX ( VALUES ( Unique_Manager[Manager] ), VAR SumBrand = CALCULATE ( SUM ( Budget_Brand[BudgetBrand] ) ) VAR SumProduct = CALCULATE ( SUM ( Budget_Product[BudgetProduct] ) ) RETURN IF ( ISBLANK ( SumProduct ), SumBrand, SumProduct ) )

Here VAR is nested deeply inside the measure code. Needless to say that this is a fantastic feature of VAR. I have been trying to use that feature in my measures with no luck. For example, this works:

Measure_good = CALCULATE( SUM(table[Amount]) )

While this does not work:

Measure_bad = CALCULATE( VAR inside_measure = SUM(table[Amount]) )

What are the rules for using VAR in this unusual way?


<h3>Answer1:</h3>

A VAR statement needs a matching RETURN. You can declare several variables after one another, but then you must use a RETURN command to pass the variables back to the main calculation, where the variable can then be used for evaluation in an IF statement or whatever

I have not tested this, but your Measure_bad should be like this (although it doesn't make much sense to use a VAR if all the remainder of the formula returns the variable)

Measure_bad = CALCULATE( VAR inside_measure = SUM(table[Amount]) RETURN inside_measure )
<h3>Answer2:</h3>

When you use a variable, via a VAR statement, you must always include a RETURN statement to essentially tell DAX you are going to consume those variables and return a value. In your last example, you do not include a RETURN statement and, therefore, you will get an error. In your first example, you have a RETURN statement, thus, no error. In your second example, you are not using variables.


<h3>Answer3:</h3>

I have found a relevant information in documentation:

https://docs.microsoft.com/en-us/dax/var-dax#remarks

<blockquote>

An expression passed as an argument to VAR can contain another VAR declaration.

When referencing a variable:

<ul><li>

Measures cannot refer to variables defined outside the measure expression, but can refer to functional scope variables defined within the expression.

</li> <li>

Variables can refer to measures.

</li> <li>

Variables can refer to previously defined variables.

</li> <li>

Columns in table variables cannot be referenced via TableName[ColumnName] syntax.

</li> </ul></blockquote>

来源:https://stackoverflow.com/questions/57769750/dax-var-defining-named-variables-in-the-middle-of-the-measure-code

Recommend

  • Problem with my update statement --getting error 3144
  • DAX VAR defining named variables in the middle of the measure code
  • Java: Invalid Character Constant error occurring
  • Is it possible to dynamically import modules?
  • Counting the number of hits to my webpage
  • UnsafePointer no longer works in swift 3
  • Critical sections in ARM
  • Linq: Find Element in a Collection
  • Acumatica - Add additional buttons to Actions drop down to screen CT30100
  • Using pdfclown few search keywords are not highlighting in chinese/japanese documents
  • Can someone explain how Yii minimizing assets is supposed to work on Heroku?
  • Can Node.JS on OpenShift be upgraded?
  • jQuery YQL SELECT FROM rss variable
  • Validating my form with Jquery
  • Django REST framework - HyperlinkedRelatedField with additional parameter
  • System.Drawing.DrawString() weird wrapping of long string
  • About global variables in Node.js
  • Windows biometric framework sample umdf driver: This device cannot start. (Code 10)
  • content must have a ListView whose id attribute is 'android.R.id.list'
  • C# code can't “see” the methods in my C++ dll
  • SQL Server version 612 , 655?
  • how can i get selectedRange.location value?
  • Generate and export point cloud from Project Tango
  • Disable account chooser FirebaseUI React
  • All Event listing on specified date in Google Calender api (V3) in java?
  • How to run chrome.tabs.insertCSS from the background page on each page?
  • JavaScript Regex to Match Boundaries of Words with diacritics
  • How to warp text around image in iOS?
  • Send array to next viewcontroller iOs xcode [duplicate]
  • Spring Boot fails to start
  • Debug `Unexpected end of JSON input Error` on content script
  • Why my AngularJS async test in Jasmine 1.3.x is not working?
  • Angular 4: Responsive Grid List
  • Cross compile glibc for arm, got undefined reference to some unwind functions
  • How do I use TagLib-Sharp to write custom (PRIV) ID3 frames?
  • CAS 4 - Not able to retrieve the LDAP groups after successful authentication
  • JavaScript RegExp Replace