Thank you Alberto for this great tutorial video! Personally for me it contains following important subtopics: 05:25 - nested variables 09:00 - common real use case for RELATEDTABLE function, where & why we should use it. 10:00 - restriction: you can NOT reference ColumnName inside Table variable. 11:45 - variable can not be blindly used as name for expression.
I didn't know that a variable is only calculated once even when the filter context is changed. I'm sure this will save me from making a mistake at some point, thanks so much. 👌
Hi Alberto ! Thanks for the tutorial. Please clarify this. While calculating Best Product Sales, why did you use nested SUMX. How is it calculated as it is summed inside already? Would using calculate be better and give the same value? Sales of Top 10 Products = VAR top10 = TOPN(10, 'Product', [Sales Amount],DESC) VAR Result = CALCULATE(SUMX ( Sales, Sales[Quantity] * RELATED('Product'[unit Price])), top10) RETURN Result
Thanks for this. There were a few mentions of performance but I can't see how variables can affect performance, does anyone have a link to explain the connection to performance?
Here we go: www.sqlbi.com/articles/optimizing-duplicated-dax-expressions-using-variables/ www.sqlbi.com/articles/optimizing-if-conditions-using-variables/ www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/
I was thinking through an issue I was having with variables at the end of last week, and thought that your other variables videos could do with some additional and more thorough explanation. This video does the trick for 🏌🏽♀👌🏽Thank you 🙏🏽
Hi, In a visual, if I bring in the Products column and the Best products Sales, it gives me all the Products with the Total for only the Top 10 products. However, when I use Best products Sales with Brands, i get the desired result. Can you please help to understand what's happening in the backend here? Why is my sales not getting restricted to only the Top 10 product's sale? Thanks in advance Best Regards, Neha
Is there a way to create a single var that can be referred by different measures? Also, if we have a measure that calculates, for instance, difference in sales between this year and last year. Can we somehow use the calculated result of this measure to calculate the YOY sales without sending a new query for the sales difference again in the YOY measure (considering both measures are in the same visual in the report)?
I think the key objectives here are readability and being succinct. Plain expressions are great for brevity but personally I like to see the expression broken down with VAR. It has the same process of defining and calling functions in other computer languages, and makes debugging easier. I was taught one function should return just on result.
Can you please share a video, how to make chart similar to Gant chart with a time slot of 1 hr with a duration of an activity. Like employe login / logout ( date time column). Is there any chart which can plot hour slot duration of an activity, like a Gant chart in bi.
In general, in programming languages there is a culture to document above a function, the author, the parameters or the functionality of the code, is this used in DAX?
You could do that, but you should write the comment after the measure assignment in the Power BI Desktop user interface. It's not common unless the measure is long, because you remove the ability to quickly see the measure definition when it's short enough.
Ciao, Do you have ( on line or in book) any advice regarding whrere to place a variable; I recenttely had the code for a calucalted column, With VAR := SUMX ( Table1, VAR aregion = Table1[Region] RETURN RELATED ( Table2[Budget] ) / COUNTROWS ( FILTER ( ALL ( Table1 ), Table1[Region] = aregion ) ) ) I was orignally placing hte variable at hte very start and then the expression, it only started to work when tthe variable was declared within SUMX? I'm thinking its to do with var being calculated once and the context that takes place in but don't really know,
That's how it works: the variable must be defined where you want to assign the right value, which is the topic of this article+video. Read the article in the description!
Great vídeo, Alberto! My questión is: is it better to use variables inside a measure or create a measure and use that measure inside other measures? Which is better for readibility and/or faster? Thanks!
It depends. The variable guarantees a single evaluation, a measure does not. A variable is local to an expression, a measure can be evaluated in multiple place.
Not clear, what do you mean? The variable stores the result of an expression, and from there you use it as a constant value. The measure does not do the same, because it is evaluated every time you reference it and it could produce different results every time depending on the evaluation context.
No SUM() does not iterate a table, therefore you cannot reference two columns in one sum expression. Also the logic is wrong because you do not want to take the sum of quantity and then multiply it by the sum of the price, in this particular case you need to use CALCULATE to modify the filter context to remove the filters and return the total sales value and then divide the sales value by the total sales value.
Alberto, it’s early here in Brazil, so good morning! You said that a VAR is evaluated only once during execution of the code. However, it seems to me that this is not always true. When you show us that a VAR can be declared in other places, you declare two (Quantity and NetPrice) inside an iterator (SUMX). In this particular case, the two variables will be evaluated many times, one for each iteration. Am I correct? Thank you!
VAR is evaluated only once where you assign it, then it can also be read. In an iterator, this process is repeated for each iteration. The meaning of the sentence is that a variable is like a constant, once assigned it cannot be changed.
the most confusing thing is Variable (essentially writing a measure that likely already exists). ie Total Customer almost every example is writing actual measures as VAR
Alberto, I was making some code using variables to store a table that contained customers that bought more than a certain value thinking that using a variable to store the table was the fastest way to achieve my computation. The code was something like this: Customers > 1000 = var customers1000 = FILTER(Customer, [sales amount] > 1000) return SUMX( customers1000, [sales amount] ) But checking with dax studio this version was actually faster: Customers > 1000 2 = SUMX( Customer, IF([sales amount]>1000, [sales amount],BLANK()) ) And I can't grasp my head around the reason why the second version is faster than the first... Can you help me?
It depends on the execution plan, which depends on many other factors. However, if you only look at the performance it's probably better this: SUMX ( Customer, VAR SalesAmount = [Sales Amount] RETURN SalesAmount * (SalesAmount > 1000) )
Hello Alberto, Thank you for an excelent video. Would you plese show how to optimize the measure attached below? The measure calculates over a few milions of records VAR CurrentTradeID = SELECTEDVALUE('REPO'[Trade ID]) VAR MAX- = ROUND(MAXX( FILTER(ALL('REPO'); 'REPO'[Trade ID] = CurrentTradeID); ('REPO'[NC1]));0) VAR TOTAL = ROUND(CALCULATE(SUMX('REPO';[1_MAX Notio]); ALLSELECTED());0) RETURN IF( HASONEVALUE('REPO'[Trade ID]); MAX_; TOTAL)
This is amazing! Thank you for sharing! Observation: If we are using the same expression multiple times, I suppose we can define a measure and use it in another measure, in which we can use VARs to make the code more readable. Can we Define a measure within another measure?
DEFINE MEASURE is part of the DAX query syntax to define a query measure and cannot be used in a measure definition - see dax.guide/st/measure/ and www.sqlbi.com/tv/computing-a-measure-in-dax-studio/
What about the customers with no sales? They will not be counted by this measure. I think you should always use the dimention table and then explicitly filter it if you don't want to include all customers in the calculation.
Great video - even though for me variables make the code more difficult to read as they are often used across different measures so I am unable to just find reason for error in one place. I think it is nice to use when you are the one that defines them - but when there is a report that I take over, I simply hate them. EDIT: defining variable as table totally didn't work for me; often when I watch Power BI related content I wonder if people showing things as on video are using some special version or wtf is going on. I am fluent with VBA and Excel, but all the Power BI stuff is just broken from my perspective.
2 years into my Power BI / DAX journey, today you brought a new light into my life: variables are actually constants. Ha! Grazie, Alberto!
Thank you Alberto for this great tutorial video!
Personally for me it contains following important subtopics:
05:25 - nested variables
09:00 - common real use case for RELATEDTABLE function, where & why we should use it.
10:00 - restriction: you can NOT reference ColumnName inside Table variable.
11:45 - variable can not be blindly used as name for expression.
excellent, thank you!
I love you so much!!! Thank you for making my life easier! 😊 After an hour of finding the solution, I have found it here. 😊😊
The "Ciao,friends" is back!!! Have a great day Mr. Ferrari
I didn't know that a variable is only calculated once even when the filter context is changed. I'm sure this will save me from making a mistake at some point, thanks so much. 👌
You are always surgical in methodology. Congratulations.
Hi Alberto ! Thanks for the tutorial.
Please clarify this.
While calculating Best Product Sales, why did you use nested SUMX. How is it calculated as it is summed inside already?
Would using calculate be better and give the same value?
Sales of Top 10 Products =
VAR top10 = TOPN(10, 'Product', [Sales Amount],DESC)
VAR Result = CALCULATE(SUMX ( Sales, Sales[Quantity] * RELATED('Product'[unit Price])), top10)
RETURN Result
Thanks Alberto! Amazing work and explanations!
Thanks for this. There were a few mentions of performance but I can't see how variables can affect performance, does anyone have a link to explain the connection to performance?
Here we go:
www.sqlbi.com/articles/optimizing-duplicated-dax-expressions-using-variables/
www.sqlbi.com/articles/optimizing-if-conditions-using-variables/
www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/
Thank you & appreciate it @alberto ❤
I was thinking through an issue I was having with variables at the end of last week, and thought that your other variables videos could do with some additional and more thorough explanation. This video does the trick for 🏌🏽♀👌🏽Thank you 🙏🏽
Thank you !!! Help’s a lot
Hi,
In a visual, if I bring in the Products column and the Best products Sales, it gives me all the Products with the Total for only the Top 10 products.
However, when I use Best products Sales with Brands, i get the desired result. Can you please help to understand what's happening in the backend here? Why is my sales not getting restricted to only the Top 10 product's sale?
Thanks in advance
Best Regards,
Neha
Is there a way to create a single var that can be referred by different measures? Also, if we have a measure that calculates, for instance, difference in sales between this year and last year. Can we somehow use the calculated result of this measure to calculate the YOY sales without sending a new query for the sales difference again in the YOY measure (considering both measures are in the same visual in the report)?
Not yet.
I think the key objectives here are readability and being succinct. Plain expressions are great for brevity but personally I like to see the expression broken down with VAR. It has the same process of defining and calling functions in other computer languages, and makes debugging easier. I was taught one function should return just on result.
Can you please share a video, how to make chart similar to Gant chart with a time slot of 1 hr with a duration of an activity. Like employe login / logout ( date time column). Is there any chart which can plot hour slot duration of an activity, like a Gant chart in bi.
Thanks for this video
Great job. Thanku
In general, in programming languages there is a culture to document above a function, the author, the parameters or the functionality of the code, is this used in DAX?
You could do that, but you should write the comment after the measure assignment in the Power BI Desktop user interface. It's not common unless the measure is long, because you remove the ability to quickly see the measure definition when it's short enough.
Ciao,
Do you have ( on line or in book) any advice regarding whrere to place a variable;
I recenttely had the code for a calucalted column,
With VAR :=
SUMX (
Table1,
VAR aregion = Table1[Region]
RETURN
RELATED ( Table2[Budget] )
/ COUNTROWS ( FILTER ( ALL ( Table1 ), Table1[Region] = aregion ) )
)
I was orignally placing hte variable at hte very start and then the expression, it only started to work when tthe variable was declared within SUMX? I'm thinking its to do with var being calculated once and the context that takes place in but don't really know,
That's how it works: the variable must be defined where you want to assign the right value, which is the topic of this article+video. Read the article in the description!
@@SQLBI Thank you.
Hi good evening
A doubt, can I use variables in Power Pivot or only in BI?
Thank you so much 😊
They are available in Power Pivot as well!
@@SQLBI thank you 🙏 so much 😃
Great vídeo, Alberto!
My questión is: is it better to use variables inside a measure or create a measure and use that measure inside other measures? Which is better for readibility and/or faster?
Thanks!
It depends. The variable guarantees a single evaluation, a measure does not. A variable is local to an expression, a measure can be evaluated in multiple place.
@@SQLBI so you end up writing the same measure many many many times????? How do you maintain that, rather one measure ie Total Customers
Not clear, what do you mean? The variable stores the result of an expression, and from there you use it as a constant value. The measure does not do the same, because it is evaluated every time you reference it and it could produce different results every time depending on the evaluation context.
12:50
Would this works as well?
Pct =
DIVIDE(
SUMX( Sales, Sales[Qty] * Sales[Price]),
SUM( Sales[Qty] * Sales[Price])
)
No SUM() does not iterate a table, therefore you cannot reference two columns in one sum expression. Also the logic is wrong because you do not want to take the sum of quantity and then multiply it by the sum of the price, in this particular case you need to use CALCULATE to modify the filter context to remove the filters and return the total sales value and then divide the sales value by the total sales value.
One question out of this topic :) , is it possible to integrate the Server Timings and clear cache to Dax.do ?
No, it's not possible to do that.
Why we need to use SUMX (RELATEDTABLE ( Sales ), Sales[Quantity] * 'Product'[Unit Price] ) and not just [Sales Amount] ?
Alberto, it’s early here in Brazil, so good morning!
You said that a VAR is evaluated only once during execution of the code. However, it seems to me that this is not always true.
When you show us that a VAR can be declared in other places, you declare two (Quantity and NetPrice) inside an iterator (SUMX). In this particular case, the two variables will be evaluated many times, one for each iteration. Am I correct?
Thank you!
VAR is evaluated only once where you assign it, then it can also be read. In an iterator, this process is repeated for each iteration. The meaning of the sentence is that a variable is like a constant, once assigned it cannot be changed.
the most confusing thing is Variable (essentially writing a measure that likely already exists). ie Total Customer
almost every example is writing actual measures as VAR
Alberto, I was making some code using variables to store a table that contained customers that bought more than a certain value thinking that using a variable to store the table was the fastest way to achieve my computation. The code was something like this:
Customers > 1000 =
var customers1000 = FILTER(Customer, [sales amount] > 1000)
return
SUMX(
customers1000,
[sales amount]
)
But checking with dax studio this version was actually faster:
Customers > 1000 2 =
SUMX(
Customer,
IF([sales amount]>1000, [sales amount],BLANK())
)
And I can't grasp my head around the reason why the second version is faster than the first...
Can you help me?
It depends on the execution plan, which depends on many other factors. However, if you only look at the performance it's probably better this:
SUMX ( Customer, VAR SalesAmount = [Sales Amount] RETURN SalesAmount * (SalesAmount > 1000) )
Hello Alberto,
Thank you for an excelent video.
Would you plese show how to optimize the measure attached below?
The measure calculates over a few milions of records
VAR CurrentTradeID = SELECTEDVALUE('REPO'[Trade ID])
VAR MAX- =
ROUND(MAXX(
FILTER(ALL('REPO'); 'REPO'[Trade ID] = CurrentTradeID);
('REPO'[NC1]));0)
VAR TOTAL =
ROUND(CALCULATE(SUMX('REPO';[1_MAX Notio]); ALLSELECTED());0)
RETURN
IF(
HASONEVALUE('REPO'[Trade ID]);
MAX_;
TOTAL)
Excellent
This is amazing! Thank you for sharing!
Observation: If we are using the same expression multiple times, I suppose we can define a measure and use it in another measure, in which we can use VARs to make the code more readable.
Can we Define a measure within another measure?
You can define and use a variable in any measure, but the scope of a variable is always local to the scope where you define it.
@@SQLBI I've seen a DAX statement called Define Measure before. Can that be used in a measure definition also?
DEFINE MEASURE is part of the DAX query syntax to define a query measure and cannot be used in a measure definition - see dax.guide/st/measure/ and www.sqlbi.com/tv/computing-a-measure-in-dax-studio/
👍
EXCELLENCT!!
What about the customers with no sales? They will not be counted by this measure. I think you should always use the dimention table and then explicitly filter it if you don't want to include all customers in the calculation.
The example counts the customer that have sales.
Great video - even though for me variables make the code more difficult to read as they are often used across different measures so I am unable to just find reason for error in one place. I think it is nice to use when you are the one that defines them - but when there is a report that I take over, I simply hate them.
EDIT: defining variable as table totally didn't work for me; often when I watch Power BI related content I wonder if people showing things as on video are using some special version or wtf is going on.
I am fluent with VBA and Excel, but all the Power BI stuff is just broken from my perspective.
Im confused why you would say to use a Variable when in doubt? Before you said this, you noted the example using variable that should not be used.