TAME the the beast that is DAX in Power BI (aka how to learn DAX)

[@nickdoy7019]

I feel like Dax functions follow the pareto principle in that around 20% of the functions are used 80% of the time. That being said, maybe a follow up video on your 20% to learn right out of the gate as a sign post would help a lot of people out. Certainly Calculate will be in there. Great video Patrick!

[@mikel5180]

What I came up with: SUM SUMX CALCULATE FILTER ALL MAX MAXX AVERAGE AVERAGEX MIN MINX COUNT/DISTINCTCOUNT COUNTROWS DISTINCT PREVIOUSDAY DATEDIFF CALENDAR RELATED TOTALYTD CALCULATETABLE

[@RamRand]

The ability to visually see the output of tables in DAX studio as one iterates through their DAX formula was a game changer for me in terms of learning DAX, great stuff!

[@GuyInACube]

Great to hear that Ben! 👊

[@RevusDeonauth]

Patrick, your videos have been an immense help over the years, thanks. Please continue to make more!

[@danmeiss]

Great video Patrick. I love these “teach you how to learn” style videos mixed in with the straight forward “here’s how this feature works” style ones.

[@GuyInACube]

Thanks. It was fun making this one.

[@ShabnamKhan-vk7fj]

This is exactly what I have been looking for all these days- for someone to put it all together for me. Sure, we can learn all the bits and pieces here and there but no one tied the strings together like you did in this video. This is awesome, thanks so much for the tutorial!

[@dilipwarrier5199]

Very true!

[@dinoben9527]

It definitely was a great help to have a showcase of the iterative process of a DAX measure creation. I usually directly write the statement in Power BI without understanding what happens behind the scene. I tend to under-utilize DAX Studio and now I will start having the habit of using it more. Thank you for the knowledge sharing!

[@DJ_Q]

I've been watching Guy In a Cube for a few days now. When I noticed the problem where the December date was returned, EOMONTH(,0) came to mind. Then when I saw you type it... I felt so smart. The COALESCE function has also been of great use to me. Thank you for what you guys do. You have earned a subscriber.

[@chrismengel3176]

Another great example of how to use the tool to help you use the tool. Love it! Have started playing with DAX Studio, but this is the first video that illustrates HOW to use it. DAX Guide is no joke - excellent resource for learning and frequently used bookmark (as is Guy In A Cube!). Keep up the great work!

[@DarkOri3nt]

Nice video Patrick. Dax is a tricky beast. I find the dax website often is very techy and not written in plain simple to understand English. And doesn't always provide an example of the function in action unlike the Microsoft sql server examples like on the help websites from ms. The query action in dax studio makes you need to understand summerizecols as power bi offers measure writing without the need to understand how to query the dax engine. Which isnt simple to understand at first. I'd say its tailored more to excel formula devs and not those used to writing expressions in ssrs for example. I think this is why people are finding it tricky in general to learn. There is also the star schema concept to understand first aswell as the model determines the difficulty of the measures.

[@shaunwthompson]

Your videos always help out! This one was exceptional; I have been underutilizing DAX Studio and now I'm going to keep it up all the time to work through problems. Amazing; thanks!

[@sue_bayes]

Love this work flow and the use of a small dataset and then iterating through using DAX Studio. Big thumbs up

[@GuyInACube]

Thank you Susan! 👊

[@dilipwarrier5199]

Really this cleared lots of hurdles in learning to write DAX functions. Thanks a lot, Patrick! God Bless you!

[@johnadair4979]

Totally cool...especially at the end with &" /3" to make it appear as a fraction instead of trying to make it an actual fraction. Brilliant.

[@jaimederks]

Thanks for this example Patrick, It's really a gift how you can explane and put a smile on my face every single time! You tamed the DAX beast a bit more for me thanks again.

[@timbader898]

Brilliant video Patrick! Been catching up on a few of your videos recently and they're all really good - and this one was even better! I learned to treat DAX like SQL (or any other language, for that matter) and to start small and build up your query from there. Great tip about using a test dataset too.

[@sahiladya8473]

The Definitive Guide To DAX helping me a lot

[@AbhishekKumar-bh6is]

Do u have PDF

[@sahiladya8473]

@@AbhishekKumar-bh6is no man. I bought hard copy from Amazon

[@BernatAgulloRosello]

Best investment ever. I have it as kindle edition

[@GuyInACube]

Same here, one of my all time favorite books.

[@GodParticleZero]

What's weird is that I found a programming language like c++ made perfect sense, but dax confuses the hell out of me lol

[@lucasjao91]

agreed... SQL makes too much more sense to me.

[@rachelrdyer]

Great video Patrick, passing it on to lots of other people. Thanks!

[@harrydaw4196]

Just learning PBI/Power query and of course DAX - this and other videos on this page feel very useful

[@TejNarayanan]

WOW.. too good Patrick, that's what I was searching for!! Thanks

[@solangefranco3444]

You are an awesome PBI teacher! 🤗💥 Thanks !

[@gravestoner2488]

Great video. Thank you. Ive been struggling to model a very messy dataset, they have so many unecessary columns, and i had to spend a lot of time validating how to produce the calculations needed for the reports. This has been an experience for sure

[@amolbdesai]

Great Video Patrick!!! I loved the three step approach of learning how to learn the functions. 👍

[@gkool4655]

So grateful for this video. Major Big up and respect Patrick and Adam

[@johnnywinter861]

Niiiice. The only thing that I do different to this is that I always define the measure at the top of the query. It keeps it outside of my query for debugging, but also means I can reference it in other queries if I want.

[@rupeshbeyondtodisability]

Hi Patrick.. I am also using excel file data to make any complicated dax.. by debugging every step to know the result... your really great..

[@jimayas]

Patrick! This is fantastic stuff as always... BUT, tell us honestly, how much time did it actually take you both to design it in your mind and execute. One thing is true. It needs time and patience and of course change in mindset to go to the DAX world....

[@yoray_s]

Very nice video Patrick and the team. I wish there was a dax debugger that will allow us to focus on each formula expression easily: What a function argument expects, what it actually get, what is return. When the DAX is short and the data is simple or premade you can find the problem easily. On the other hand longer DAX formulas and larger data may have unexpected values which makes the whole thing much more challenging. Adding to that, sometimes in order to really understand what a function does and how it works, you need to experience it on your own data.. it is much easier to do so anyway.. rather than building a sample data every time you want to check something out. Totally appreciate the video, it does helps a bit, yet in order to really "tame the beast" we need a serious tool.. :)

[@Milhouse77BS]

kzbin.info/www/bejne/b4S5Y4mkjseanZY

[@yoray_s]

​@@Milhouse77BS once again, not efficient way to debug code (or fully answers the requirements above) Debug shouldn't be a tedious task.

[@rossleaver2210]

Awesome Video Patrick. I love being taught how to “fish”.

[@BarnabyWaights]

Fantastic, thanks ! I’m so used to excel where you can (more easily ) verify your outputs and iterate quickly. I see now I need to get into DAX studio properly!

[@nixb6542]

Patrick you are awesome! Thank you for all your amazing videos.

[@gavclark66]

Really cool video ! I need to start spending more time with DAX Studio after watching this !

[@GuyInACube]

YES! 👊

[@mwaltercpa]

“I’m not teaching you how to write DAX, I’m teaching you how to *learn* to write DAX” Love this. Also, where has COALESCE been my whole DAX life!? Thanks Patrick!

[@ameyraj4947]

Awesome. That's all you need validate and iterate in dax. Helpful video Patrick 🎉

[@GuyInACube]

Yes, iterate, iterate, iterate. Thanks Amey.

[@sarajvo2010]

Moment when and how use CALCULATE in this case is great way of learning how to write DAX step by step. Great example!

[@GuyInACube]

CALCULATE solves all problems or does it? Thanks for watching.

[@briancanfield2968]

By far the best video I've ever seen on this topic

[@GuyInACube]

Thanks Brian. DAX is challenging, but if we can learn the functions, life and DAX can be simpler.

[@djstone5518]

What do I think? Love your videos and great tips especially on this topic. Many thanks!

[@Freekepediamrito]

I want to count the days of operation based on the start date, a sampling date, until there is an end date. This to be able to calculate how many days of the month are applied and to be able to pay my associates.

[@guilhemdelorme9089]

Thanks Patrick, this is a cool video as always and a great use of DAX studio! I personally find that the big challenges in teaching DAX to 'citizen developers' are the concepts of relations and filter context, so far from Excel but so powerful and yielding 'cleaner' models. My take is, if you are not properly teached about these concepts, you will struggle in understanding the functions, as you suggest. Any recommendation for resources that my 'citizen developers' can look at on these topics?

[@ekaterinasukhanova4321]

Thank you for making Power BI DAX FUN!!!!

[@scottwilliams6741]

Great overview of how to learn DAX functions Patrick! Thanks for this tip on this tool!

[@GuyInACube]

Thanks @Scott Williams.

[@AJM2183]

Fantastic video Patrick! I wish my company could use time functions, but we're non-fiscal. Makes it tons harder as there are no pre-built functions to deal with time for me.

[@AJM2183]

@GHD Bowie Training Yeah for certain. I'd absolutely love a video on it, and I'd be happy to share my successes.

[@jaimemejia4387]

Sometimes DAX could be frustrating when you are starting to learn, these techniques definetely help a lot to make it easier.

[@berksezerylmaz2212]

in 6:24 how does the summarizecolumns function knows "visits" is the name of measure and the measurement calculation is countrows(clientisits). because the syntax of summarizecolumns functions shows a complete different sort of values.

[@fr1sket363]

One of the best Dax videos i've watched so far and no it's not my first one haha.

[@GlobalStevie]

*Mind blown* Game changer. Thank you!

[@franciscoclaudio4818]

Great tip! But if you have more than one visit per month, how does the formula behave?

[@bitips]

Great video Patick..... Tks

[@Nazeerul_Hazard]

Patric thanks for showing art of writing DAX :)

[@divyakoshatwar]

Thank you, It was very helpful

[@hermesjrd]

Excellent Video Patrick, Thanks a lot

[@GuyInACube]

Thanks for watching Hermes.

[@oricchannel2811]

I learn DAX with Guy in a Cube !!!! Thank you for sharing...

[@sarojkumarmaharana3906]

Patrick, you have tried to make this very simple. But where is the starting point of a guy like me who is entering into such a Pandora's box? Which you feel are the simplest functions I should start with?

[@WendersonReisss]

Hello! Your videos is amazing! Could you create a video teaching us how to create "First Pass Yield" chart?

[@MySpreadsheetLab]

Amazing video thanks Patrick!

[@MySpreadsheetLab]

Content and video production too!

[@GuyInACube]

Our pleasure! Glad you enjoyed it. 👊

[@legalizaya]

Great video Patrick, thanks again!

[@GuyInACube]

Thanks Thierry.

[@andreasmohr7054]

Coooooooooool!!!!!! Thank you! I learned from this video that it´s only normal if I don´t "get it" yet. Now I can start to tame that beast - reeeeeal cautiously :-)

[@ajaaskelainen]

I was wondering how much DAX Studio should I use. It turns out a lot!

[@BernatAgulloRosello]

Add tabular editor to the mix and you'll go next level

[@isi2cute]

Thanks Patrick.. Very helpful

[@GuyInACube]

Hello Isioma, thanks for watching and happy it helped you.

[@lifeisgreat11]

What about period based on (from) selected month?

[@ramonbernabeu3174]

Pure Gold this video!

[@snicket87]

Great Video! It is helping me with my "column addction", where you get addicted to use custom columns! haha

[@GuyInACube]

Yes, calculated columns are so tempting.

[@JLB0880]

Seriously sometimes I feel like I need columnholics anonymous...

[@BrainyBrunetteBarbie]

@@GuyInACube Yo, custom columns are pretty sweet though.

[@ekaterinazamosha4149]

Thank you! Great video!

[@LandscapeInMotion]

I think DAX is challenging because the functions are not very well explained in the doco as they can be ambiguous, with many use cases. None the less, I still manage to get my DAX queries working in the end..

[@GuyInACube]

That's why I prefer dax.guide as they have some good examples for the functions. As with anything, there are always a lot of options and it can be hard to determine what is the best approach and in some cases it depends on your data. The best approach for one model, may not be the same for another.

[@mahkoblackburn6811]

Such a good "meta-lesson"!

[@aravindl3540]

Hi Patrick, Can you explain how to pull values from different columns in a second table based on the values from a column in the first table.. Say if I have values (2020, 2021 etc) in a year column in Table A, based on those values selected, I need to pull the value from column 2020 or from Column 2021 in table B. It would be really helpful.

[@jeffkasavan93]

In Excel that’s either Vlookup, or Index/Match, or Xlookup. In Power BI -> Get Data -> from Excel -> file location -> Table A … repeat steps for Table B. Then in Power BI create a date table. In the Power BI data model page, create a relationship between the year columns in Table A and Table B

[@jeffkasavan93]

…and the year column in the Date table.

[@mnowako]

That’s a good one 👍 Thanks!

[@GuyInACube]

Thanks for watching Mariusz.

[@EduardoCortezOrtega]

Los vídeos de este canal tienen muchas menos vistas de las que realmente merecen, gracias por compartir tanto

[@kim1217]

Really helpful 👍

[@GuyInACube]

Great to hear! 👊

[@shanefairbairn7141]

Fantastic video!

[@ChrisWagnerDatagod]

Oh I love this.

[@GuyInACube]

Thanks Chris. It was fun the make.

[@cparedes2302]

Great video! I really like the Guy in a Cube videos! I am new to Power BI but I am facing a “funny” problem. I wonder if you can give me some hints. I am analyzing COVID data. The data comes in 3 different data series: 1. By date when the result of the test was delivered 2. By date when the test was performed 3. By date when the person first felt a symptom All the measures apply in similar way to all 3 series. 8 have completed one series and have a zillion measures. So I am looking for something like SUBROUTINES in DAX. I though that Calculation Groups could be of help but I just saw the Tabular Editor is actually a paid tool and I wish to stick with free tools as I am just doing this for fun. I would like to have a visual where I can select the data series and I can also select the measure to show Can you give me some tips? I am studying PARAMETERS as it seems a possible way to do it Any help is welcome! Greetings from Guatemala Carlos

[@balasubramanyam5104]

Hi Patrick Can we give chance to user change the visuals/charts dynamically (I mean we can give set of visuals in slicer so user can select in slicer which is required eg: bar chart, line chart ) I know it is not related to this video any input's will helpful. Thanks

[@Game_Impala]

Love your videos! Go Hogs!

[@MrRecombinant]

DAX isn't hard ... it's just challenging. Different word, same thing IMO!

[@kyos7569]

Excellent video, thanks a lot for the help and guidance!

[@GuyInACube]

Thanks Kyos. Is this what you were looking for? If not, please feel free to provide more context here.

[@BernatAgulloRosello]

Didn't know about the coalesce Function! Always going with if isbank thing. This is much shorter

[@GuyInACube]

COALESCE just makes it so much shorter. Thanks for watching Bernat.

[@chriskeo392]

Coming from python. ..this is just learning a new syntax Dammit!

[@micahdail]

It strikes me that Kyos was looking to segment his/her customer list with the assignment of the distinct count of months over the last 3 months that the customer visited. It's not an issue of visit counts, it's an issue of visit-month counts. My proposal for the calculated column on the client table: Client[Visit Segment] = VAR _datebeg = EOMONTH ( TODAY (), -3 ) + 1 VAR _dateend = TODAY () VAR _monthcount = CALCULATE ( DISTINCTCOUNT ( 'Calendar'[MonthEndDate] ), FILTER ( 'Calendar', ( 'Calendar'[Date] >= _datebeg ) && ( 'Calendar'[Date]

[@BernatAgulloRosello]

Relatedtable filters do anything at all? Just curious

[@micahdail]

@@BernatAgulloRosello There are probably better ways to do this, and the Italians would probably scold me for my code, but yes, I used RELATEDTABLE() to activate the visit table to ensure it is using the filter context of the visit when calculating the distinct months from the calendar. I don't want just months from the calendar, I want months from the calendar that have visits. Hence activating the visit context.

[@GuyInACube]

​ @Micah Dail Hmmm. Didn't think about it that way. Great Point. At least we agree on using EOMONTH and COALESCE :).

[@hhhohlick]

I think that more proper measure will be = VAR _NumberOfMonthsVisited = CALCULATE ( COUNTROWS ( FILTER ( VALUES ( 'Calendar'[YearMonth] ), NOT ISEMPTY ( RELATEDTABLE ( 'Table' ) ) ) ), DATESINPERIOD ( 'Calendar'[Date], EOMONTH ( TODAY (), 0 ), -3, MONTH ) ) RETURN COALESCE ( _NumberOfMonthsVisited, 0 ) & "/3" or almost similar = VAR _NumberOfMonthsVisited = CALCULATE ( COUNTROWS ( ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Calendar'[YearMonth] ), "Count", COUNTROWS ( RELATEDTABLE ( 'Table' ) ) ) ), DATESINPERIOD ( 'Calendar'[Date], EOMONTH ( TODAY (), 0 ), -3, MONTH ) ) RETURN COALESCE ( _NumberOfMonthsVisited, 0 ) & "/3"

[@BernatAgulloRosello]

@@hhhohlick Alberto Ferrari would probably go with crossfilter instead. But I didn't know you cloud use relatedtable to that effect

[@hanguyen.2289]

Super helpful!!

[@jangelojr]

Amazing, thanks

[@monirmonir2555]

Good video brother

[@komengemwandila5499]

I was working with a donut chart today. The client wanted to see the breakdown per category, but they also wanted to see the total. I noticed that donut chart has so much room in the middle. Why can't they give us an option to display the total there.

[@hahahahaha191990]

that’s a visual constraint but to get past this, simply utilize a card visual and place it in middle of your donut chart, then group them up for easier rearrangement.

[@arifkashmiree4798]

Zabar Dast ( Wonder full)

[@BrentIngram]

this is awesome!

[@MarekHouska]

Thanks! 👍

[@GuyInACube]

Thanks Marek 👊

[@sierrathompson4078]

Omg you were my professor at southern

[@abinashkhamari]

Hi Patrick.. i hope you are doing good.. i have an issue.. i cannt see the External tools tab in my ribbon even when i have installed the latest version of it, i even went through the video by Adam but of no help . could you please suggest anything

[@DataAnalyticsPro21]

"Unable to create a temporary file. Setup Aborted. Error 5: Access is denied" Not able to install DAX studio.

[@ankurkhaparde6641]

amazing !!!

[@ravishukla725]

please find the sample data below for your reference, dataset1 :- dataset 2 Id amount ID amount 1 1300 1 1300 2 1300 2 1100 3 1100 3 500 4 500 now if finding first largest is easy max(amount) issue is with 2nd largest is not easy as amount is same in first dataset . could you help me write an dax which give me second highest values from both dataset .

[@diegomex85]

very very very very thank, and thank to pt-br subs =) you are unbelievable, tame the Dax, tame Powr BI, Tame the Data \o/

[@koutlaw30]

Awesome!!!

[@GuyInACube]

Thanks Kendall and thanks for watching.

[@Porterki]

Consider the beast tamed!

[@JoseSoto-fk7sb]

Great!!

[@GuyInACube]

Woot, happy you like it Jose.

[@sange419]

one rule , Patrick ruleZ

[@gulhermepereira249]

I'm gonna go after anyone who dislikes this masterpiece

[@JBartlett_DMU]

While attempting to download the sample files just now, even though I'm already logged-in to my account on the Guy in a Cube website, it still asked me for my name and email address. Is this a glitch with the website?

[@GuyInACube]

Are you still getting the error? Can you try again tomorrow? If the problem persist, please reply to this comment.

[@JBartlett_DMU]

@@GuyInACube I checked again, and the same issue still persists. See my previous comment for a detailed description of the problem.

[@azharshaik21]

Thanks Patrick, is there any way to export the data from power query to share Point Via excel?

[@GuyInACube]

You could try using R as part of Power Query, but I don't know anything native. Thanks for watching.

[@azharshaik21]

@@GuyInACube Thanks for the response. I will try with R

[@DerBarde2012]

Nice but your howto implicits using a specific tool, which is sometimes out of scope...