MS Excel Toronto Meetup - What is the DAX CALCULATE Function and how does it work -Wyn Hopkins
Learn how to AUTOMATE YOUR EXCEL REPORTS in minutes instead of hours without copy-paste or coding: https://snapreportschamp.com/course
Get this ready-to-use Excel solution for 2 and 3 columns of Dependent Dropdown Lists:
https://solveandexcel.ca/dynamic-depe...
_______________________________________________________________________
Session recorded live on March 8, 2023
Writing DAX is easy, writing the correct formula is hard. Understanding the concepts of how DAX works differently from Excel is one of the biggest hurdles to Power Pivot and Power BI adoption by the Excel crowd.
Having learned DAX initially from Rob Collie's book - "Power Pivot and Power BI" it took Wyn a while to realize that the CALCULATE function is not a superior SUMIFS, and thinking of it that way hindered his understanding of more complicated DAX functions.
Wyn explains how DAX works to people, especially Excel users.
---------------------
About the Speaker: Wyn Hopkins
Wyn Hopkins is a Microsoft MVP, trainer, consultant, and author of the book “Power BI for the Excel Analyst”
Based in Perth, Western Australia, Wyn is the director of the Power BI and Excel consulting firm “Access Analytic”. Their YouTube channel has 50,000+ subscribers and he co-runs the Perth Power BI and Modern Excel meetup group. A long time ago he was a chartered Accountant with PriceWaterhouseCoopers but Wyn and team now spend their time delivering Power BI and Excel training along with reporting solutions for clients of all industries and sizes.
--------------
Find more about Wyn's work and connect with him:
Social Media and other links: https://wyn.bio.link/
Book and Website: https://pbi.guide/
Company https://accessanalytic.com.au/
Timestamps
00:00:00 - Celia Alves MS Excel Toronto news and upcoming events
00:04:30 - Celia Introduces Wyn Hopkins
00:07:00 - Wyn introduces the presentation
00:09:25 - Wyn briefly describes the DAX formula language, power pivot and the data model (alternative to XLOOKUP – use diagram view relationships)
00:14:15 - CALCULATE function is a supercharged SUMIF() (Rob Collie quote), demo of SUMIFS() in Excel compared to data model power pivot
00:17:20 - Create a new measure in DAX based on SUMIFS Excel formula replacing SUMIFS() with CALCULATE(SUM(…))
00:19:30 - Think of CALCULATE as the ‘change filter function’, in Excel use the AGGREGATE function & ignore hidden rows argument to filter out hidden rows, compare result to data model
00:28:00 - Calculate Prior Year sales and Sales lifetime to date (running total) - logic and measures explained
00:38:00 - DAX measures can modify filters, using DAX formatter websites, edit measure from the PowerPivot Measures dropdown experience
00:43:10 – Create inactive relationships between tables; helper functions in DAX (OPENINGBALANCEYEAR etc.); create date table; helper measures
00:50:40 – CALCULATE has a hidden filter ALL which can remove filters
00:54:15 – Inactive relationships to change filters
00:58:00 – Questions: Date types/formats; overriding hidden ALL in CALCULATE (KEEPFILTERS); resources available; Power Query change dates Using Locale to pick date source format; Power Query vs Power BI or Power Pivot uses; documenting code; discussion about VBA; emojis
01:28:20 - Wrap up