...did NOT know that a variable could hold a virtual table! Disappointing that you can't reference its columns, but it still is an option that I could have used for my project last week! Thank you, Ruth!
My goodness, what a useful pattern for a common problem - count of rows in an aggregate that meet a criteria, such as your "Count of Customers who have purchased more than X products". This is a compelling argument for understanding variables in DAX, and gives us an insight for *why* we should expand our understanding beyond basic measures.
Hi! I have a comment to the last section of the video. I'm pretty sure I have been able to access the columns of a table stored in a variable. I created a summarized table with a filter and added a calculated column with ADDCOLUMNS as a variable and the RETURN COUNTROWS(FILTER(table_as_a_variable; [Column added with ADDCOLUMNS] > 500) ) works just fine. It does have it's limits though, referencing it as table_as_a_variable[Column name] does not work, but just referencing the column works. Also a strange thing that I noticed while testing was that if in the RETURN statement I use for example RETURN COUNT([Column]) it did give me the option of choosing from the columns of the "original" summarized table, but not the column I added with ADDCOLUMNS. So some things work and some not quite!
Hi. Have you got any video on Percentile? I have looked on the youtube but have not found anything specifically about the function itself. I would be grateful if you could talk about it.
Hi, Ruth. Thanks for the deep explanation of VAR here. Can we understand the VAR in order to create a virtual column or table to help us do the next step calculation? I first get in touch with VAR is to help us delete interim calculate columns. In your video, you just directly created them in the DAX. I can see it gives reference to the calculate for the DAX in the next step. I am curious about the main purpose to use the VAR and when shall we use it?
Hi Ruth, just curious if you think they'll lift the restriction that you can't reference columns inside of variables that are tables. It doesn't make sense (at least to me) that they can't do it...and it makes the code ugly in certain situations. Being able to treat a table variable exactly like a table would be excellent.Thanks!Scott
I have no clue... If it is there, it has to be due to technical issues (wild guess)? Ask them in the power bi community, they might have an explanation for it. /Ruth
I really wish PBI allowed global variables. I.e., variables that can be referenced outside of the measure - anywhere in the app. Similar to Qlikview variable functionality. Do you know of a work-around for this?
Interesting! So you can't reference a virtual table in a variable, but once you wrap the virtual table in a FILTER statement, it turns the virtual table into a regular table since FILTER is a table function? Thanks for the insight. Pretty cool.
Hi Ruth! This video was very helpful to understand using variables in DAX! I am aiming to recreate a Present Value table for a loan which has more than one cash flow. I have searched a lot in Power BI Community, but could not find any answers. Would you be able to help me on this? I can send you a sample Excel file, to show what I'm trying to recreate in Power BI. Thanks in advance!
fantastic video,i will follow your channel. have u also a newsletter or a blog? i don't imagine yet how. anyway,i haven't understood well how with a variable the performance are improved. thanks
That's good explanation about variables 👍 can we reference any date to variables? specifically previous months? like what is previous months date when current and previous mo tha sales are same. if you can make video on that it ill be appreciated :)
Very informative. But, I wonder if writing too much DAX would make the process of report authoring less easy to manage ? What would have been nice is a way to keep all DAX code in a GIT controlled file . Similar to how HTML treats style sheets and Javascript files.
There are different user groups that do power bi development: IT/ power bi developers and business users. Yes for the first group, definitely no for the second. Hope it makes sense!
Hey Ruth! Marco Russo wrote about calling measures in VAR , and he shows that there is improvements. Is that what you asked on 6:40 min on the video? anyway, check he's article out: www.sqlbi.com/articles/optimizing-if-conditions-using-variables/ Thank's for the videos you make!!!
Hi Ruth, Thanks for your Videos. They are greate :) I'm pretty new to pbi and I have a problem. I have data like this, in columns: Creation date: 03.04.18, 01.02.19... Name: A, B, C, D. Sales: 1, 0... I need to calculate a rolling average for sales from last 12 months and for each month for A and for A + B + C. How can I do it, based on this criterium. I tried everything what I found, but it doesn't work for me. Maby someone has an idea and could help me. Thank you so much and have a nice day :)
@curbal So I tried Post my problem in pbi community. But it doesn't work. I can not post something. I have to give my password again and again. Any ideas?
I would add to the "naming variables" topic this blog: www.sqlbi.com/blog/marco/2019/01/15/naming-variables-in-dax/. In other words, it's considered a best practice to prefix your variables with underscores just to be sure later you won't face an error (and generally you should never-ever have a table prefixed with an underscore, obviously).
Hello, if we get a little deeper - with iterators like SUMX you have to declare variables within its brackets otherwise row context is unaccesible and you get aggregation result not sum of iterations. like in this case if you wouldn't count but would like to get some of orders the result might be wrong ;)
I have been following all your DAX Friday Videos and I must say you are a wonderful teacher
God Bless you Ruth
Thanks 😊
/Ruth
...did NOT know that a variable could hold a virtual table! Disappointing that you can't reference its columns, but it still is an option that I could have used for my project last week! Thank you, Ruth!
Then I have done my job properly 😊
Have a great weekend Hachi,
/Ruth
My goodness, what a useful pattern for a common problem - count of rows in an aggregate that meet a criteria, such as your "Count of Customers who have purchased more than X products". This is a compelling argument for understanding variables in DAX, and gives us an insight for *why* we should expand our understanding beyond basic measures.
Hi Gina, Variables are amazing!! :)
/Ruth
Hi! I have a comment to the last section of the video. I'm pretty sure I have been able to access the columns of a table stored in a variable. I created a summarized table with a filter and added a calculated column with ADDCOLUMNS as a variable and the RETURN COUNTROWS(FILTER(table_as_a_variable; [Column added with ADDCOLUMNS] > 500) ) works just fine. It does have it's limits though, referencing it as table_as_a_variable[Column name] does not work, but just referencing the column works.
Also a strange thing that I noticed while testing was that if in the RETURN statement I use for example RETURN COUNT([Column]) it did give me the option of choosing from the columns of the "original" summarized table, but not the column I added with ADDCOLUMNS. So some things work and some not quite!
Can we store text in variables ?
getting error msg. Pls help
In_Cus1 =
Var a = VALUES(Sales[Brand])
var b = VALUES(Sales[Customer])
var c = a & b
return
SUMX ( c, MAX( [Qty_Diff], 0 )
Hi. Have you got any video on Percentile? I have looked on the youtube but have not found anything specifically about the function itself. I would be grateful if you could talk about it.
Hi Jawed,
Not yet, but I will add it to my list!
/Ruth
I am trying to have the variable store multiple values using ‘if’ condition . Is that possible in Dax ?
I dont know, never tried to do that, did you? Did it work?
/Ruth
Could you? I am trying to do that
I don't know exactly, but I think you didn't show the output of the last variable.
HI Ruth , how can choose the related PBI file for each video ?
The number on the video title is the same as the one in the download center
@@CurbalEN in the dowload center the maximum number 98, at the time you have related vedio #121 for example .
Just go to page 2 to see more files
Hi, Ruth. Thanks for the deep explanation of VAR here. Can we understand the VAR in order to create a virtual column or table to help us do the next step calculation? I first get in touch with VAR is to help us delete interim calculate columns. In your video, you just directly created them in the DAX. I can see it gives reference to the calculate for the DAX in the next step. I am curious about the main purpose to use the VAR and when shall we use it?
Thanks so much Ruth! This had been really helpful :)
Hi Ruth, just curious if you think they'll lift the restriction that you can't reference columns inside of variables that are tables. It doesn't make sense (at least to me) that they can't do it...and it makes the code ugly in certain situations. Being able to treat a table variable exactly like a table would be excellent.Thanks!Scott
I have no clue... If it is there, it has to be due to technical issues (wild guess)?
Ask them in the power bi community, they might have an explanation for it.
/Ruth
I really wish PBI allowed global variables. I.e., variables that can be referenced outside of the measure - anywhere in the app. Similar to Qlikview variable functionality.
Do you know of a work-around for this?
I am afraid not, but I am sure they are working on it!
/Ruth
thank you Ruth... another awesome tutorial... !
🥳🥳
Ruth, this is excellent. Thanks Jason
Happy Friday Jason :)
/Ruth
Interesting! So you can't reference a virtual table in a variable, but once you wrap the virtual table in a FILTER statement, it turns the virtual table into a regular table since FILTER is a table function? Thanks for the insight. Pretty cool.
Hi Ruth! This video was very helpful to understand using variables in DAX!
I am aiming to recreate a Present Value table for a loan which has more than one cash flow. I have searched a lot in Power BI Community, but could not find any answers. Would you be able to help me on this? I can send you a sample Excel file, to show what I'm trying to recreate in Power BI. Thanks in advance!
Very intresting! Thanks Ruth
You welcome !!
/Ruth
fantastic video,i will follow your channel.
have u also a newsletter or a blog?
i don't imagine yet how.
anyway,i haven't understood well how with a variable the performance are improved.
thanks
Wonderful to hear!
I do have a newspaper , but send only 2 a year. UA-cam is where I am most active!
/Ruth
That's good explanation about variables 👍
can we reference any date to variables?
specifically previous months?
like what is previous months date when current and previous mo tha sales are same.
if you can make video on that it ill be appreciated :)
Thank you Ruth!
You welcome!
/Ruth
Very informative. But, I wonder if writing too much DAX would make the process of report authoring less easy to manage ? What would have been nice is a way to keep all DAX code in a GIT controlled file . Similar to how HTML treats style sheets and Javascript files.
There are different user groups that do power bi development: IT/ power bi developers and business users.
Yes for the first group, definitely no for the second.
Hope it makes sense!
Brilliant stuff cheers Ruth
Thanks Dimiliv and happy Friday!
/Ruth
Hi Ruth, excellent video. Just an idea: why don’t you make a video about Power BI “quick measures” functionality?? It would be awesome!
Hi Juan!
I never use them so it is difficult for me to show them “fairly” if you know what I mean, by let me try !
Thanks for the idea!
/Ruth
Thanks Ruth
You welcome :)
/Ruth
Variables can also have capital letters, which makes them more readable
Yes! CamelCasing is a great tip ! Thanks Vida :)
/Ruth
Hey Ruth! Marco Russo wrote about calling measures in VAR , and he shows that there is improvements. Is that what you asked on 6:40 min on the video? anyway, check he's article out:
www.sqlbi.com/articles/optimizing-if-conditions-using-variables/
Thank's for the videos you make!!!
Uhh! Thanks!! :)
/Ruth
Hi Ruth,
Thanks for your Videos. They are greate :)
I'm pretty new to pbi and I have a problem.
I have data like this, in columns:
Creation date: 03.04.18, 01.02.19...
Name: A, B, C, D.
Sales: 1, 0...
I need to calculate a rolling average for sales from last 12 months and for each month for A and for A + B + C. How can I do it, based on this criterium.
I tried everything what I found, but it doesn't work for me. Maby someone has an idea and could help me. Thank you so much and have a nice day :)
Hi!
Can you check with the Power bi Community? Here is how and why:
m.ua-cam.com/video/oXFOebuvRz0/v-deo.html
/Ruth
@@CurbalEN thanks :) I was there, but I couln't solve my specific problem
Make sure you post sample data and the DAX measures you have already tried and you will get help.
/Ruth
Make sure you post sample data and the DAX measures you have already tried and you will get help.
/Ruth
@curbal So I tried Post my problem in pbi community. But it doesn't work. I can not post something. I have to give my password again and again. Any ideas?
Excellent solution with FILTER, but I was curious about the shirt of Mexico.
What’s not to love about Mexico? 😉
/Ruth
@@CurbalEN I do not have the answer, I love everything about Mexico.
That is good to know that you cannot reference columns from a "variable table".
Yes! Variabel table! Why didnt I think of that?
Thanks and have a nice weekend!
/Ruth
I would add to the "naming variables" topic this blog: www.sqlbi.com/blog/marco/2019/01/15/naming-variables-in-dax/. In other words, it's considered a best practice to prefix your variables with underscores just to be sure later you won't face an error (and generally you should never-ever have a table prefixed with an underscore, obviously).
Nicccce. Thanks!
Hi Nir!! Glad you like it :)
/Ruth
Hello, if we get a little deeper - with iterators like SUMX you have to declare variables within its brackets otherwise row context is unaccesible and you get aggregation result not sum of iterations. like in this case if you wouldn't count but would like to get some of orders the result might be wrong ;)
Master class! 👏👏👏
Happy Friday!
/Ruth