TAME the the beast that is DAX in Power BI (aka how to learn DAX)
HTML-код
- Опубликовано: 9 мар 2021
- We know that DAX, in Power BI, can be intimidating. We've been there. Patrick shows you how you can learn DAX using what you have available today and TAME the beast that is DAX!
Download Sample: guyinacu.be/tamedaxsample
DAX.Guide: dax.guide
DAX Studio: daxstudio.org/
DAX Patterns: daxpatterns.com
Microsoft Learn: docs.microsoft.com/learn/path...
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #DAX #GuyInACube 
Наука
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!
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
Patrick, your videos have been an immense help over the years, thanks. Please continue to make more!
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!
Very true!
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!
Great to hear that Ben! 👊
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.
Thanks. It was fun making this one.
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!
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!
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!
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.
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.
Really this cleared lots of hurdles in learning to write DAX functions. Thanks a lot, Patrick! God Bless you!
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!
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.
Great Video Patrick!!!
I loved the three step approach of learning how to learn the functions. 👍
WOW.. too good Patrick, that's what I was searching for!! Thanks
Awesome Video Patrick. I love being taught how to “fish”.
Patrick you are awesome! Thank you for all your amazing videos.
So grateful for this video. Major Big up and respect Patrick and Adam
Love this work flow and the use of a small dataset and then iterating through using DAX Studio. Big thumbs up
Thank you Susan! 👊
Great video Patrick, passing it on to lots of other people. Thanks!
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.
Just learning PBI/Power query and of course DAX - this and other videos on this page feel very useful
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.
Great video Patick..... Tks
*Mind blown* Game changer. Thank you!
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..
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
You are an awesome PBI teacher! 🤗💥 Thanks !
Thank you, It was very helpful
What's weird is that I found a programming language like c++ made perfect sense, but dax confuses the hell out of me lol
agreed... SQL makes too much more sense to me.
Thank you! Great video!
I learn DAX with Guy in a Cube !!!! Thank you for sharing...
Pure Gold this video!
Thank you for making Power BI DAX FUN!!!!
Awesome. That's all you need validate and iterate in dax. Helpful video Patrick 🎉
Yes, iterate, iterate, iterate. Thanks Amey.
Great overview of how to learn DAX functions Patrick! Thanks for this tip on this tool!
Thanks @Scott Williams.
Really cool video ! I need to start spending more time with DAX Studio after watching this !
YES! 👊
“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!
Moment when and how use CALCULATE in this case is great way of learning how to write DAX step by step. Great example!
CALCULATE solves all problems or does it? Thanks for watching.
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.
Amazing, thanks
One of the best Dax videos i've watched so far and no it's not my first one haha.
Fantastic video!
Super helpful!!
By far the best video I've ever seen on this topic
Thanks Brian. DAX is challenging, but if we can learn the functions, life and DAX can be simpler.
Excellent Video Patrick, Thanks a lot
Thanks for watching Hermes.
The Definitive Guide To DAX helping me a lot
Do u have PDF
@@AbhishekKumar-bh6is no man. I bought hard copy from Amazon
Best investment ever. I have it as kindle edition
Same here, one of my all time favorite books.
Great video Patrick, thanks again!
Thanks Thierry.
Such a good "meta-lesson"!
What do I think? Love your videos and great tips especially on this topic. Many thanks!
Patric thanks for showing art of writing DAX :)
Excellent video, thanks a lot for the help and guidance!
Thanks Kyos. Is this what you were looking for? If not, please feel free to provide more context here.
Thanks Patrick.. Very helpful
Hello Isioma, thanks for watching and happy it helped you.
this is awesome!
Hello!
Your videos is amazing! Could you create a video teaching us how to create "First Pass Yield" chart?
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?
Sometimes DAX could be frustrating when you are starting to learn, these techniques definetely help a lot to make it easier.
Love your videos!
Go Hogs!
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.
That’s a good one 👍 Thanks!
Thanks for watching Mariusz.
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 :-)
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.
@GHD Bowie Training Yeah for certain. I'd absolutely love a video on it, and I'd be happy to share my successes.
amazing !!!
Really helpful 👍
Great to hear! 👊
Amazing video thanks Patrick!
Content and video production too!
Our pleasure! Glad you enjoyed it. 👊
Thanks! 👍
Thanks Marek 👊
Great tip!
But if you have more than one visit per month, how does the formula behave?
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....
Good video brother
Zabar Dast ( Wonder full)
Awesome!!!
Thanks Kendall and thanks for watching.
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.
What about period based on (from) selected month?
DAX isn't hard ... it's just challenging. Different word, same thing IMO!
Coming from python.
..this is just learning a new syntax
Dammit!
Great Video! It is helping me with my "column addction", where you get addicted to use custom columns! haha
Yes, calculated columns are so tempting.
Seriously sometimes I feel like I need columnholics anonymous...
@@GuyInACube Yo, custom columns are pretty sweet though.
Omg you were my professor at southern
Oh I love this.
Thanks Chris. It was fun the make.
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.. :)
ruclips.net/video/9SV2VnYbgg4/видео.html
@@Milhouse77BS once again, not efficient way to debug code (or fully answers the requirements above) Debug shouldn't be a tedious task.
very very very very thank, and thank to pt-br subs =)
you are unbelievable, tame the Dax, tame Powr BI, Tame the Data \o/
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
Great!!
Woot, happy you like it Jose.
Los vídeos de este canal tienen muchas menos vistas de las que realmente merecen, gracias por compartir tanto
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
I was wondering how much DAX Studio should I use. It turns out a lot!
Add tabular editor to the mix and you'll go next level
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?
Didn't know about the coalesce Function! Always going with if isbank thing. This is much shorter
COALESCE just makes it so much shorter. Thanks for watching Bernat.
one rule , Patrick ruleZ
Consider the beast tamed!
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
"Unable to create a temporary file. Setup Aborted. Error 5: Access is denied" Not able to install DAX studio.
"It's not hard, it's just challenging"
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..
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.
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.
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.
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.
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
…and the year column in the Date table.
I'm gonna go after anyone who dislikes this masterpiece
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]
Relatedtable filters do anything at all? Just curious
@@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.
@Micah Dail Hmmm. Didn't think about it that way. Great Point. At least we agree on using EOMONTH and COALESCE :).
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"
@@hhhohlick Alberto Ferrari would probably go with crossfilter instead. But I didn't know you cloud use relatedtable to that effect
Last time only Pragmatic Works
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 .
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?
Are you still getting the error? Can you try again tomorrow? If the problem persist, please reply to this comment.
@@GuyInACube I checked again, and the same issue still persists. See my previous comment for a detailed description of the problem.