Advanced DAX [Heavy Webinar]

[@CoopmanGreg]

I liked the way you encouraged students to be persistent with trying to understand the techniques and theory, saying that it is hard and even yourself had some struggles gaining the knowledge. Too many times, instructors act like everything is so simple, when it is not. Thanks for your honesty. I think it shows you care about your students learning. Thanks!

[@robofski]

Great explanation of how Filter works, finally got it and it gave me an idea to solve a problem I'd been having with a custom calendar and previous year data where using the built in Time Intelligence function just didn't quite work! Thanks!

[@sarojiniewijesiriwardana7318]

Thanks Mitchell. Can you pls tell us how to create a separate table for calculations?

[@affiliatenetwork3533]

I'm not quire sure why we are seeing any information for the year of 2011 if we are using the sold date as the first filter in the houses available on the market measure. Looking at the data, there weren't any houses sold in 2011, only listed houses but our condition is Sold date greater or equal to min date. Yes, our min date probably contains values from 2011, but if our starting point is the first sold date in 2012 and the end point is the last listing date, why do we still have data shown for 2011?

[@MitchellPearson]

The filter function will evaluate each row in the MLS table, keep in mind the ALL function removes all filters from the table so that the filter function will have access to ALL rows in the MLS table. Any row where the sold date was after 1/1/2011 and the listing date was before 12/31/2011 will get evaluated to true and returned. Now, let's look at a scenario for clarification. If a home was listed on 3/15/2010 and it didn't sell until 6/6/2012, was it on the market for sale in 2011? The answer is yes. The listing date was before 12/31/2011 (3/15/2010) and the sold date was after 1/1/2011 (6/6/2012). I hope this example helps, thanks!

[@JoshuaDHarvey]

@@MitchellPearson Great clarity here, thank you! I love that the discussion can keep going on these videos even a few years later as people like me scavenge the web for answers and information related to DAX. Huge fan of PragmaticWorks, so again thank you for these great resources! Mitchell, I was hoping to understand the technical reason for why the 'Date '[Year] in that Chart Table does influence the columns inside the MAX() and MIN() functions. I know in this example the ALL() was wrapped around the 'MLS' table but assuming you had done something simpler with just 1 column on the left sides of the Boolean operators, such as 30 day running total, you could have used a ALL() on the Date table and then something such as: 'Date'[Date] >= MIN('Date'[Date]) && 'Date'[Date]

[@kennethstephani692]

Great video!

[@ahmedal-dossary4386]

You have shared an effective trick to workaround such problem. Is there a way to force DAX to return YTD in case we have month / months with no sales? I can’t imagine creating a fictitious records with 0 sales for each month with no sales.

[@mynameisyuriorlov]

examples are great. I learnt many things

[@JohnOttoKnoke]

Great video and easy to understand,

[@JoshuaDHarvey]

52:15 Hey Mitchell, first of all, thank you for another incredible webinar. I'm a couple years behind watching this but love all the webinar's you guys publish. For the last 2 days I've been really digging around trying to find a good explanation about the MAX() or MIN() on the other side of a Boolean operator and why that still respects the page level filtering context. I've seen similar formulas such as 30 day running total but I've only assumed that I just need to memorize that anything on the other side of the Boolean operator or inside a MIN(), MAX() will the page level filtering still apply. Is that not correct or how should I think about how the engine in handling and respecting external (implicit) filtering in accordance with MAX() AND MIN().

[@MitchellPearson]

Hello Joshua, min('date'[date]) here can be further defined as: "return the minimum date from the date table within the current filter context." The min and max function here are still evaluated within the context of any filters being applied. Therefore, when the year is 2011, min will return 1/1/2011 and max will return 12/31/2011. This is obviously a good thing and the intended behavior. Best Regards.

[@premjitchowdhury262]

I don't think sales were any where near in 2008 due to recession

[@mynameisyuriorlov]

yes but quality is poor :(

[@soumenin]

Dude Mitchell. Seems, like you want to spread fear among Audiences while describing "S" curve to your DAX teaching. Spread Love, Not fear..