DAX Fridays!

[@reanalytics1863]

I have been following all your DAX Friday Videos and I must say you are a wonderful teacher God Bless you Ruth

[@CurbalEN]

Thanks 😊 /Ruth

[@islamabdel-ghany2564]

Can we store text in variables ?

[@solarson_studio]

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?

[@scottpowell803]

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

[@CurbalEN]

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

[@HachiAdachi]

...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!

[@CurbalEN]

Then I have done my job properly 😊 Have a great weekend Hachi, /Ruth

[@ginameronek8068]

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.

[@CurbalEN]

Hi Gina, Variables are amazing!! :) /Ruth

[@bhargavimurthy]

I am trying to have the variable store multiple values using ‘if’ condition . Is that possible in Dax ?

[@CurbalEN]

I dont know, never tried to do that, did you? Did it work? /Ruth

[@jlotica]

Could you? I am trying to do that

[@extrov1]

HI Ruth , how can choose the related PBI file for each video ?

[@CurbalEN]

The number on the video title is the same as the one in the download center

[@extrov1]

@@CurbalEN in the dowload center the maximum number 98, at the time you have related vedio #121 for example .

[@CurbalEN]

Just go to page 2 to see more files

[@jawedneshat5147]

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.

[@CurbalEN]

Hi Jawed, Not yet, but I will add it to my list! /Ruth

[@jasonwoodward5501]

Ruth, this is excellent. Thanks Jason

[@CurbalEN]

Happy Friday Jason :) /Ruth

[@lydiak9]

Thanks so much Ruth! This had been really helpful :)

[@tlee7028]

thank you Ruth... another awesome tutorial... !

[@CurbalEN]

🥳🥳

[@jason.campbell474]

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?

[@CurbalEN]

I am afraid not, but I am sure they are working on it! /Ruth

[@sureshpunna205]

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 )

[@vishaljhaveri7565]

I don't know exactly, but I think you didn't show the output of the last variable.

[@bradj229]

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.

[@juangut4531]

Hi Ruth, excellent video. Just an idea: why don’t you make a video about Power BI “quick measures” functionality?? It would be awesome!

[@CurbalEN]

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

[@SudarshanAdhyapak]

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!

[@magnumbi8396]

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

[@CurbalEN]

Wonderful to hear! I do have a newspaper , but send only 2 a year. KZbin is where I am most active! /Ruth

[@murrrmur]

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!

[@pmc806]

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 :)

[@sau002]

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.

[@CurbalEN]

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!

[@dirksmeyers877]

Very intresting! Thanks Ruth

[@CurbalEN]

You welcome !! /Ruth

[@Dimiliv1981]

Brilliant stuff cheers Ruth

[@CurbalEN]

Thanks Dimiliv and happy Friday! /Ruth

[@zhou4168]

Thank you Ruth!

[@CurbalEN]

You welcome! /Ruth

[@vida1719]

Variables can also have capital letters, which makes them more readable

[@CurbalEN]

Yes! CamelCasing is a great tip ! Thanks Vida :) /Ruth

[@jasonwoodward5501]

Thanks Ruth

[@CurbalEN]

You welcome :) /Ruth

[@andrecavasotero6234]

Excellent solution with FILTER, but I was curious about the shirt of Mexico.

[@CurbalEN]

What’s not to love about Mexico? 😉 /Ruth

[@andrecavasotero6234]

@@CurbalEN I do not have the answer, I love everything about Mexico.

[@christopherhastings2142]

That is good to know that you cannot reference columns from a "variable table".

[@CurbalEN]

Yes! Variabel table! Why didnt I think of that? Thanks and have a nice weekend! /Ruth

[@idakwiatkowska2862]

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 :)

[@CurbalEN]

Hi! Can you check with the Power bi Community? Here is how and why: m.kzbin.info/www/bejne/pYmpgJiYqtuFsJI /Ruth

[@idakwiatkowska2862]

@@CurbalEN thanks :) I was there, but I couln't solve my specific problem

[@CurbalEN]

Make sure you post sample data and the DAX measures you have already tried and you will get help. /Ruth

[@CurbalEN]

Make sure you post sample data and the DAX measures you have already tried and you will get help. /Ruth

[@idakwiatkowska2862]

@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?

[@brunof.s.8186]

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!!!

[@CurbalEN]

Uhh! Thanks!! :) /Ruth

[@arklur3193]

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).

[@NirRobinson]

Nicccce. Thanks!

[@CurbalEN]

Hi Nir!! Glad you like it :) /Ruth

[@jevgenijskaktins1066]

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 ;)

[@CurbalEN]

Master class! 👏👏👏 Happy Friday! /Ruth