DAX Fridays!

Подписаться 138 тыс.

Просмотров 12 тыс.

50% 1

Lets do a deep dive into variables shall we? In this video, I am going to cover the following:
1. Naming variables, dos and donts
2. The syntax for calling variables
3. Can a variable refer to a measure?
4. Can a measure refer to an external variable?
5. Can a variable refer to a variable?
6. Can a variable store a table?
6. Can you refer to a column from the table inside the variable?
Get Northwind Dataset: • Northwind data source:...
Link to DAX Fridays survey: bit.ly/2MMM4KK
Here you can download all the pbix files: curbal.com/don...
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/cou...
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/...
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/pro...
Many thanks in advance!Here you can download all the pbix files: curbal.com/don...
▲▲▲▲▲▲▲▲▲▲
Our PLAYLISTS:
Join our DAX Fridays! Series: goo.gl/FtUWUX
Power BI dashboards for beginners: goo.gl/9YzyDP
Power BI Tips & Tricks: goo.gl/H6kUbP
Power Bi and Google Analytics: goo.gl/ZNsY8l
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#daxfridays #curbal #dax #powerbi #mvp

Опубликовано:

21 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 69

@reanalytics1863 4 года назад

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

@CurbalEN 4 года назад

Thanks 😊 /Ruth

@lydiak9 3 года назад

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

@HachiAdachi 5 лет назад

...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 5 лет назад

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

@tlee7028 2 года назад

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

@CurbalEN 2 года назад

🥳🥳

@ginameronek8068 5 лет назад

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 5 лет назад

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

@jasonwoodward5501 5 лет назад

Ruth, this is excellent. Thanks Jason

@CurbalEN 5 лет назад

Happy Friday Jason :) /Ruth

@solarson_studio 4 года назад

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?

@bradj229 3 года назад

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.

@scottpowell803 5 лет назад

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 5 лет назад

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

@zhou4168 4 года назад

Thank you Ruth!

@CurbalEN 4 года назад

You welcome! /Ruth

@SudarshanAdhyapak 4 года назад

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!

@jawedneshat5147 5 лет назад

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 5 лет назад

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

@vishaljhaveri7565 2 года назад

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

@islamabdel-ghany2564 3 года назад

Can we store text in variables ?

@sau002 4 года назад

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 4 года назад

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 5 лет назад

Very intresting! Thanks Ruth

@CurbalEN 5 лет назад

You welcome !! /Ruth

@extrov1 8 месяцев назад

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

@CurbalEN 8 месяцев назад

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

@extrov1 7 месяцев назад

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

@CurbalEN 7 месяцев назад

Just go to page 2 to see more files

@juangut4531 5 лет назад

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 5 лет назад

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

@pmc806 4 года назад

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

@jason.campbell474 5 лет назад

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 5 лет назад

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

@murrrmur 4 года назад

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!

@magnumbi8396 5 лет назад

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 5 лет назад

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

@jasonwoodward5501 4 года назад

Thanks Ruth

@CurbalEN 4 года назад

You welcome :) /Ruth

@Dimiliv1981 5 лет назад

Brilliant stuff cheers Ruth

@CurbalEN 5 лет назад

Thanks Dimiliv and happy Friday! /Ruth

@sureshpunna205 3 года назад

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 )

@bhargavimurthy 5 лет назад

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

@CurbalEN 5 лет назад

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

@jlotica 4 года назад

Could you? I am trying to do that

@vida1719 5 лет назад

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

@CurbalEN 5 лет назад

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

@christopherhastings2142 5 лет назад

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

@CurbalEN 5 лет назад

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

@andrecavasotero6234 5 лет назад

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

@CurbalEN 5 лет назад

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

@andrecavasotero6234 5 лет назад

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

@NirRobinson 5 лет назад

Nicccce. Thanks!

@CurbalEN 5 лет назад

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

@jevgenijskaktins1066 5 лет назад

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 5 лет назад

Master class! 👏👏👏 Happy Friday! /Ruth

@arklur3193 5 лет назад

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

@idakwiatkowska2862 5 лет назад

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 5 лет назад

Hi! Can you check with the Power bi Community? Here is how and why: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-oXFOebuvRz0.html /Ruth

@idakwiatkowska2862 5 лет назад

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

@CurbalEN 5 лет назад

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

@CurbalEN 5 лет назад

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

@idakwiatkowska2862 5 лет назад

@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 5 лет назад

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 5 лет назад

Uhh! Thanks!! :) /Ruth