Here’s a great video demonstrating the power and flexibility of DAX with 50 real world examples you can download and follow along with the samples files.
Example Index:
- (01:27) Filter Context, First Look
- (04:40) CALCULATE to change Filter Context with a Boolean Filter
- (05:10) CALCULATE and CALCULATETABLE DAX Functions
- (06:28) What is a Boolean Filter?
- (08:00) First look at the Overwrite Operator in CALCULATE. Merge the Internal Filter Context and External Filter Context
- (09:45) CALCULATETABLE to change Filter Context with a Boolean Filter
- (11:25) CALCULATE to perform Context Transition
- (13:17) All Measures have a Hidden CALCULATE Function
- (14:20) Boolean Filter is always converted to a FILTER & ALL DAX Function construction
- (17:22) DAX Studio to look at FILTER and ALL Equivalent for a Boolean Filter
- (20:00) VALUES rather than ALL in first argument of FILTER
- (22:05) VALUES Function to bring a Variable into a Formula and use it as a condition for a Boolean Filter
- (24:22) AND Logical Test – 4 Examples
- (26:18) Can NOT use two different columns in a Boolean Filters
- (28:30) OR Logical Test Boolean Formulas
- (30:33) Can NOT Directly Compare Two Columns as a Boolean Filter. Keystone Pricing Examples.
- (33:20) DAX Studio to see how FILTER and ALL delivers a table where we directly compare two columns for a DAX Measure
- (34:00) Can NOT use MIN or MAX or other aggregate functions as a condition for a Boolean Filter
- (34:00) Frequency Distribution DAX Formula. Learn about the COUNTROWS Function
- (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
- (47:42) Look at Data Model for Power BI File for looking more closely at the Overwrite Operator
- (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
- (50:50) Reminder about ALL before we learn about ALLSELECTED. What is the problem that ALLSELECTED can solve?
- (52:35) ALLSELECTED to create Grand Totals that respect the filtering in the PivotTable
- (53:33) Context Transition is how ALLSELECTED gets to the Grand Total Cell Filter Context
- (53:45) Hidden Context Transition for Measures in an Excel PivotTable or Power BI Visual
- (54:45) ALLSELECTED and Context Transition, the Full Story
- (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
- (57:18) Use ALLSELECTED DAX Function in CALCULATE Measures
- (59:05) ALLSELECTED Functions works on Cross Tab Reports too !
- (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
- (01:01:20) Look at new Data Model for next example
- (01:02:20)First example of the KEEPFILTERS DAX Function to force an AND Logical Test rather than an Overwrite Operation
- (01:06:55) What is a Complex Filter? What is a Complex Filter Reduction Error?
- (01:08:56) How KEEPFILTERS can help solve a Complex Filter Reduction Error for Year Column in AVERAGEX.
- (01:14:47) Why KEEPFILTERS is used in the New Quick Measure in Power BI
- (01:16:33) How KEEPFILTERS can help solve a Complex Filter Reduction Error for Year Column and Month Column in CROSSJOIN in AVERAGEX
- (01:18:30) Data Model solution to solve Complex Filter Reduction Error
- (01:20:05) Determine if a filter is a complex filter with CROSSJOIN
- (01:21:17) Expanded Table Concept & Relationships.
- (01:26:50) Table Filters & Expanded Table as filters in the Filter argument of CALCULATE
- (01:27:15) Expanded Table Filter with ALL Function. 2 Examples
- (01:29:15) Table Filter to send a filter backwards across a Many-To-One Relationship. Great Visual for understanding this filter
- (01:33:10) ALLEXCEPT DAX Function with an Expanded Column
- (01:37:00) Examples of Time Intelligence Functions
- (01:37:20) Measure for Revenue from Last Year. SAMPERIODLASTYEAR
- (01:38:40) Measure for Revenue for Last Month. DATEADD and ISFILTERED DAX Functions
- (01:41:32) Compare Excel and DAX Formulas
- (01:43:10) Measure for % Change using IF, BLANK, DIVIDE and HASONEVALUE DAX Functions
- (01:46:15) DAX Formula Evaluation Context Summary