DAX Variables and where to declare them,

[dicken]

Hello,
I hope someone can gvie me some advice ( reading suggestions ) regarding variables, I'm confused.
So, Variables of defined once, and are constant, but if I use

ab:=VAR maxindex = MAX(Table1[Index]) RETURN maxindex

and then drop that into a pivot table it takes on filter context of the
Pivot table. But sometimes I've had to define the Variable within the iterator,

Copy of Measure 1:=
SUMX(
    Table1,
              VAR aregion = Table1[Regeio
RETURN
               RELATED( Table2[Budget] )    / COUNTROWS(FILTER(ALL(Table1) )
          )

I seem to be misunderstanding somthing? any suggestions or information welcome.

Richard.

 

[Matt Allington]

Your second formula does not refer to the var at all.

 

[dicken]

Your second formula does not refer to the var at all.

Sorry I've copied it wrongly; I'll update when I can find it.

Richad.

 

[dicken]

Your second formula does not refer to the var at all.

Sorry I missed the bottom part off, it should read ;

Copy of Measure 1:=SUMX(
    Table1,
    VAR aregion = Table1[Regeion]
    RETURN
        RELATED( Table2[Budget] )
            / COUNTROWS(
                FILTER(
                    ALL( Table1 ),
                    Table1[Regeion] = aregion  )  )   )

It's more the theory of variables than this particular example I'm puzzled by.
Richard

 

[Matt Allington]

Variables can be declared anywhere, anytime. The only rule is that once you start declaring variables, you must use RETURN to stop declaring them

VAR myVar = something
VAR my2ndVar = stuff
RETURN
SUMX(Table,
    VAR NewVar = yada
    VAR another = blabla
    RETURN
     myVar+my2ndVar + NewVar + Another)

 

[dicken]

Variables can be declared anywhere, anytime. The only rule is that once you start declaring variables, you must use RETURN to stop declaring them

VAR myVar = something
VAR my2ndVar = stuff
RETURN
SUMX(Table,
    VAR NewVar = yada
    VAR another = blabla
    RETURN
     myVar+my2ndVar + NewVar + Another)

Hi Matt,
in the example I gave if you write ;

VAR varname = {in this case region] 
RETURN 
SUMX(
    Table1,
        RELATED( Table2[Budget] )
            / COUNTROWS(
                FILTER(
                    ALL( Table1 ),
                    Table1[Regeion] = aregion  )  )   )

It will not work,
It has to be declared within the context, this is a link to Excel Forum where I first posted the question some time ago, I was using EARLIER and wanted to try a variable instead, I'm also happy up share a copy of the file but there is an attached copy here.

https://www.excelforum.com/office-365/1385680-dax-power-pivot-variable-versus-earlier.html#post5710221

Richard

 

[Matt Allington]

It does work, but it may not be what you want/expect. What you have just posted declares the variable before starting the SUMX. The variable will not change as you iterate through SUMX. There are cases when this is what is needed, and cases where it is not required. It depends what you want. If you want the variable to change as you iterate SUMX, then you must place it as part of the second parameter, as I posted earlier.

 

[dicken]

It does work, but it may not be what you want/expect. What you have just posted declares the variable before starting the SUMX. The variable will not change as you iterate through SUMX. There are cases when this is what is needed, and cases where it is not required. It depends what you want. If you want the variable to change as you iterate SUMX, then you must place it as part of the second parameter, as I posted earlier.

Well what I posted above

It does work, but it may not be what you want/expect. What you have just posted declares the variable before starting the SUMX. The variable will not change as you iterate through SUMX. There are cases when this is what is needed, and cases where it is not required. It depends what you want. If you want the variable to change as you iterate SUMX, then you must place it as part of the second parameter, as I posted earlier.

Honestly it doesn't, is just returns an error; just tried in afresh;

VAR aregion = Table1[Regeion]
    RETURN
    SUMX(Table1,
       RELATED( Table2[Budget] ) 
            / COUNTROWS(
                FILTER(
                    ALL( Table1 ),
                    Table1[Regeion] = aregion ) ))

Just return an error;

SUMX(
    Table1,
    VAR aregion = Table1[Regeion]
    RETURN
       RELATED( Table2[Budget] ) 
            / COUNTROWS(
                FILTER(
                    ALL( Table1 ),
                    Table1[Regeion] = aregion ) )

does give the correct result.

Richard.

 

[Matt Allington]

I believe we are talking about 2 different things. Originally I understood that you were trying to understand the syntax for variables and how it works - maybe I got that wrong. I have explained that variables can be declared outside and also inside an iterating function like SUMX. There is a second point about why you first version posted just above doesn't work. The concept of declaring a variable outside of SUMX does work, as I have stated. The reason your formula doesn't work is because it is wrong, not because of the placement of the variables.

VAR aregion = Table1[Regeion]
    RETURN
    SUMX(Table1,
       RELATED( Table2[Budget] )
            / COUNTROWS(
                FILTER(
                    ALL( Table1 ),
                    Table1[Regeion] = aregion ) ))

Line 1 above (declaring a variable) refers to a column. I call it a Naked Column because it is used in a measure and is not wrapped in a function. This is not allowed in DAX measures. You could write the formula as follows, however.

VAR aregion = SELECTEDVALUE(Table1[Regeion])
    RETURN
    SUMX(Table1,
       RELATED( Table2[Budget] )
            / COUNTROWS(
                FILTER(
                    ALL( Table1 ),
                    Table1[Regeion] = aregion ) ))

This new code is syntactically correct. Whether it gives the answer you are looking for is a completely different thing. This new code assigns the variable aregion once, outside of the iteration of Table1 inside SUMX. If that is what you want, then great. If that is not what you want, you can/should declare the variable inside the SUMX. if you declare it inside the SUMX, then you should not use SELECTEDVALUE and you CAN use a naked column, because SUMX has a row context and hence you are allowed to refer to naked columns in that table.

HTH.

 

[dicken]

I believe we are talking about 2 different things. Originally I understood that you were trying to understand the syntax for variables and how it works - maybe I got that wrong. I have explained that variables can be declared outside and also inside an iterating function like SUMX. There is a second point about why you first version posted just above doesn't work. The concept of declaring a variable outside of SUMX does work, as I have stated. The reason your formula doesn't work is because it is wrong, not because of the placement of the variables.

VAR aregion = Table1[Regeion]
    RETURN
    SUMX(Table1,
       RELATED( Table2[Budget] )
            / COUNTROWS(
                FILTER(
                    ALL( Table1 ),
                    Table1[Regeion] = aregion ) ))

Line 1 above (declaring a variable) refers to a column. I call it a Naked Column because it is used in a measure and is not wrapped in a function. This is not allowed in DAX measures. You could write the formula as follows, however.

VAR aregion = SELECTEDVALUE(Table1[Regeion])
    RETURN
    SUMX(Table1,
       RELATED( Table2[Budget] )
            / COUNTROWS(
                FILTER(
                    ALL( Table1 ),
                    Table1[Regeion] = aregion ) ))

This new code is syntactically correct. Whether it gives the answer you are looking for is a completely different thing. This new code assigns the variable aregion once, outside of the iteration of Table1 inside SUMX. If that is what you want, then great. If that is not what you want, you can/should declare the variable inside the SUMX. if you declare it inside the SUMX, then you should not use SELECTEDVALUE and you CAN use a naked column, because SUMX has a row context and hence you are allowed to refer to naked columns in that table.

HTH.

Hello Matt, sorry this is becoming something of a saga, I think the penny has dropped, I need to declare it as a naked variable,
which is why I need it 'inside' I'm in p pivot so don't have selecedtvalue, I'll try, "if hasone and vaules" just to see if it works" .
Thanks for your patience.
I hae a question coming up re Averages, different methods and efficiency.
Richard.