MSPTDA 19: CALCULATE DAX Function & Filter Context & ALLSELECTED & KEEPFILTERS (50 Examples)

Video Description

All 20 files used in video are available in this zipped folder: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/FilesUsedInVideo19.zip Download file individually at class web site: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/DAX.htm pfd notes for Video #19: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/019-MSPTDA-CALCULATE-FilterContext.pdf Comprehensive video about the CALCULATE DAX Function and how Filter Context works to calculate DAX Formulas in Power BI and in Excel Power Pivot. Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor. Topics: 1. (00:15) Introduction 2. (01:27) Filter Context, First Look 3. (04:40) CALCULATE to change Filter Context with a Boolean Filter 4. (05:10) CALCULATE and CALCULATETABLE DAX Functions 5. (06:28) What is a Boolean Filter? 6. (08:00) First look at the Overwrite Operator in CALCULATE. Merge the Internal Filter Context and External Filter Context 7. (09:45) CALCULATETABLE to change Filter Context with a Boolean Filter 8. (11:25) CALCULATE to perform Context Transition 9. (13:17) All Measures have a Hidden CALCULATE Function 10. (14:20) Boolean Filter is always converted to a FILTER & ALL DAX Function construction 11. (17:22) DAX Studio to look at FILTER and ALL Equivalent for a Boolean Filter 12. (20:00) VALUES rather than ALL in first argument of FILTER 13. (22:05) VALUES Function to bring a Variable into a Formula and use it as a condition for a Boolean Filter 14. (24:22) AND Logical Test – 4 Examples 15. (26:18) Can NOT use two different columns in a Boolean Filters 16. (28:30) OR Logical Test Boolean Formulas 17. (30:33) Can NOT Directly Compare Two Columns as a Boolean Filter. Keystone Pricing Examples. 18. (33:20) DAX Studio to see how FILTER and ALL delivers a table where we directly compare two columns for a DAX Measure 19. (34:00) Can NOT use MIN or MAX or other aggregate functions as a condition for a Boolean Filter 20. (34:00) Frequency Distribution DAX Formula. Learn about the COUNTROWS Function 21. (43:07) ALL DAX Function & Grand Totals. Learn about the Remove Operator in ALL DAX Function when you use it in the Filter argument of CALCULATE 22. (47:42) Look at Data Model for Power BI File for looking more closely at the Overwrite Operator 23. (49:00) Overwrite Operation. Second look at the Overwrite Operator in CALCULATE. Merge the Internal Filter Context and External Filter Context into the Final Filter Context 24. (50:50) Reminder about ALL before we learn about ALLSELECTED. What is the problem that ALLSELECTED can solve? 25. (52:35) ALLSELECTED to create Grand Totals that respect the filtering in the PivotTable 26. (53:33) Context Transition is how ALLSELECTED gets to the Grand Total Cell Filter Context 27. (53:45) Hidden Context Transition for Measures in an Excel PivotTable or Power BI Visual 28. (54:45) ALLSELECTED and Context Transition, the Full Story 29. (56:17) Visual Example of when ALLSELECTED will not get back to the Filter Context of the Grand Total Cell in the Excel PivotTable or Power BI Visual 30. (57:18) Use ALLSELECTED DAX Function in CALCULATE Measures 31. (59:05) ALLSELECTED Functions works on Cross Tab Reports too ! 32. (59:44) Example of Measure in Power BI Visual that uses ALLSELECTED and will not get back to the Filter Context of the Grand Total Cell Power BI Visual 33. (01:01:20) Look at new Data Model for next example 34. (01:02:20)First example of the KEEPFILTERS DAX Function to force an AND Logical Test rather than an Overwrite Operation 35. (01:06:55) What is a Complex Filter? What is a Complex Filter Reduction Error? 36. (01:08:56) How KEEPFILTERS can help solve a Complex Filter Reduction Error for Year Column in AVERAGEX. 37. (01:14:47) Why KEEPFILTERS is used in the New Quick Measure in Power BI 38. (01:16:33) How KEEPFILTERS can help solve a Complex Filter Reduction Error for Year Column and Month Column in CROSSJOIN in AVERAGEX 39. (01:18:30) Data Model solution to solve Complex Filter Reduction Error 40. (01:20:05) Determine if a filter is a complex filter with CROSSJOIN 41. (01:21:17) Expanded Table Concept & Relationships. 42. (01:26:50) Table Filters & Expanded Table as filters in the Filter argument of CALCULATE 43. (01:27:15) Expanded Table Filter with ALL Function. 2 Examples 44. (01:29:15) Table Filter to send a filter backwards across a Many-To-One Relationship. Great Visual for understanding this filter 45. (01:33:10) ALLEXCEPT DAX Function with an Expanded Column 46. (01:37:00) Examples of Time Intelligence Functions 47. (01:37:20) Measure for Revenue from Last Year. SAMPERIODLASTYEAR 48. (01:38:40) Measure for Revenue for Last Month. DATEADD and ISFILTERED DAX Functions 49. (01:41:32) Compare Excel and DAX Formulas 50. (01:43:10) Measure for % Change using IF, BLANK, DIVIDE and HASONEVALUE DAX Functions 51. (01:46:15) DAX Formula Evaluation Context Summary