Variables - The Whiteboard #05
Vložit
- čas přidán 6. 09. 2022
- A variable is a constant in DAX. Is it more clear if we draw it?
Learn abstract DAX concepts in a more interactive way with "The Whiteboard" series. Read more: www.sqlbi.com/blog/marco/2022...
#thewhiteboard - Věda a technologie
Lightbulb moment, I understand it now, great format
Más claro imposible!!! Enjoy DAX
Awesome! 🎉
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!
This is gold
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
Awesome😎😎
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.
Thanks, very helpfull
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, countrows(distinct(sales[customerskey])))
because in customers table there is customers who did not bought any product in sales table.
Thank you
It always depends on the goal to achieve - in general, you're right, but probably it was out of scope for this video.
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.
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?
You can use DEFINE MEASURE only in DAX queries, not inside a measure definition (e.g. no use in Power BI)
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.
The variable is evaluated in the evaluation context (row and filter) where you defined it. Then it's a constante and it's only used. I hope it helps!
Thank you 👍
So variables that store resulting tables are also static (meaning they do not respect data lineage in model) is that right?💕
Not really. If you store a table in a variable, the data lineage is respected once you iterate or apply the variable to the filter context.
@@SQLBI Thank you 👍
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?
The variable is evaluated in the filter context every measure the Sales_Amount measure is executed. Not sure we understand your question.
@@SQLBI You answered my question. Thank you for clarifying. Simple but not easy 🙃
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.
This article+video should clarify the issue: www.sqlbi.com/articles/naming-temporary-columns-in-dax/
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?
You must use the original column name in the iterator over a variable that contains a table.
@@SQLBI but that implies I'm using an iterator function. If I just want to do a regular average, median or sum over one column? Is the only option to use the equivalent iterator function and include that column as the only calculation inside the iterator?
@@rob123456hawke If you want to do a SUM from a Table variable you can do so like this: SUMX( Table_Variable, [Column Name]), same for average, just use averagex.
Why hurry with the pen? Take your time for handwriting. Anyway thanks for the video, your content is awesome.
... Why?
Variables have no business being in CALCULATE 😀