Variables - The Whiteboard #05

A variable is a constant in DAX. Is it more clear if we draw it?


#thewhiteboard

[RC-nnld]

Lightbulb moment, I understand it now, great format

[yeyoremix]

Very well explained and useful.

I find using Variables on row context more helpful, because they actually get to capture the different values as defined on each row calculation!

For example:

Sales Previous Day =

VAR PreviousDay = Calendar[Date] -1

Return

Calculate(Sales,

Calendar[Date] = PreviousDay)

However, this might be very resource demanding when having large datasets.

Would be awesome to see a video on it!

[carloromanelli]

Marco you rock as usual! GRAZIE for sharing your knowledge with all of us. Maybe next time, would be also very interisting to deeper analyze when make sense include a variable inside a function like CALCULATE

[MilhouseBS]

Almost did 6:00 yesterday. Almost forgot that you can't define a measure as a VAR. I suppose you could use DEFINE MEASURE to add a temporary measure inside another measure definition?

[sbn]

Thank you very much Mr. Marco. Now i don't have no complexes with Variables. Approximately it was a bit clearly that VAR is constant but I was't not sure.

[amineazeroil]

Hey Marco,
Very good what you do, i'm learning a lot with you, appreciate it.
I have a small point, maybe for Average sales it's avgsales =divide( salesamount,
because in customers table there is customers who did not bought any product in sales table.

Thank you

[inaction]

So Variable is to hold a constant result and should not be mistakenly used as if it is a Measure, which can be referred and recomputed with other expressions.

[OlsoNounas]

Thank you SQLBI. Could someone please explain why the filter doesnt work against the variable in calculate, but if I created the sales amount as a variable in a measure, e.g., Sales_Amount = VAR __SALES = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ) RETURN __SALES and then applied a slicer within the report it does filter the variable to red only?

[MrWLKNSN]

Speaking of variables: is there a way to evaluate a datatable() stored as variable against a model? Whatever I try, i cannot reference any of the columns of that "variable table".

The use case is when you want to pass a table as a variable in a dax query via the rest api to have it evaluated against an existing dataset. So far, only managed to do it with separate scalar values and not a full table.

[RobHOUTX]

Thanks! I was hoping you would address another issue I always have with table variables.

I have no idea how to reference a specific column within a result table stored in a variable.

To me it would be super obvious to do varTable[colName] but this never works.

any ideas for this?

[Bharath_PBI]

Thank you 👍
So variables that store resulting tables are also static (meaning they do not respect data lineage in model) is that right?💕

[williamarthur]

Enjoying this series, one thing I'm very unsure about is when a variable needs to be declared within an Iterator, e.g. SUMX(
Table1,
VAR aregion = Table1[Regeion]
RETURN
RELATED( Table2[Budget] )
/ COUNTROWS(
FILTER(
ALL( Table1 ),
as opposed to declaring at the very start as you would for MAX ( date ) in a cumulative total. I was re reading the chapter on Variables earlier this week and thought I'd grasped it, then decided I didn't really understand after all. any advice? I admit I have brought this up before. Sorry.

[mategombas]

Why hurry with the pen? Take your time for handwriting. Anyway thanks for the video, your content is awesome.

[Dzidza]

Variables have no business being in CALCULATE 😀