Download Excel Start Files: http://bit.ly/2N3rHGj
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn how to extract sales records based by day with a formula. Learn about the New Office 365 Dynamic Array Functions: FILTER, SORT and UNIQUE. Compare and contrast this new method to the way we used to accomplish it in the old days with complicated Array Formulas.

View on YouTube

All 20 files used in video are available in this zipped folder: http://bit.ly/2BfCEjj
Download file individually at class web site: http://bit.ly/2TrKPQT
pfd notes for Video #19: http://bit.ly/2BeWn2M
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

View on YouTube

EMT 1536: Power Query to Import Text Data for Accounting Schedule of Accounts
Download Excel Start Files: http://bit.ly/2VTHw6y
Download Excel Finished Files: http://bit.ly/2QOzNCY
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn how to create an Accounting Schedule of Accounts from a Text File using Power Query and a PivotTable with a Slicer. This solution will update when a new file becomes available next month.

View on YouTube

Download Excel File: http://bit.ly/2TFoUp4
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn two methods using Excel Spreadsheet Formulas to calculate a Unique Count of Dates Product Was Sold. Learn about the New Office 365 Dynamic Array Formulas and New Excel Calculation Engine or the Traditional Excel Array Formula Method. Learn about the new Office 365 FILTER Array Function, UNIQUE Array Function. See the standard Excel Functions: FREQUENCY, IF, SUM, ROWS and COUNT.

Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)

View on YouTube

Download Start files:
http://bit.ly/2SnL4fe
Zipped Folder with data that was already loaded into previous file: http://bit.ly/2Ax85FJ
Download Other Start Files: http://bit.ly/2SnL5ji
http://bit.ly/2AnzlpP
http://bit.ly/2SnL5Qk
Zipped Folder with data that was already loaded into previous file: http://bit.ly/2AkF9Aw
Download Finished files:
http://bit.ly/2StC4oX
http://bit.ly/2ApVN1D
http://bit.ly/2SnL6nm

pdf Notes: http://bit.ly/2Aox3GY
Assigned Homework:
Download Word Document and read: http://bit.ly/2SnL7rq
Then download the rest of the files and complete the homework:
http://bit.ly/2Ah6cN9
Examples of Finished Homework:
http://bit.ly/2SnKWfK
http://bit.ly/2Ah6eof

This video teaches you about the power and pitfalls of DAX Iterator Functions and DAX Table Functions in Excel Power Pivot and in Power BI.
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. (00:31) Summary of what we know so far in class about Iterators and Table Functions
3. (01:38) Summary of what we will learn in this video about Iterators and Table Functions
4. (02:51) What does DAX stand for? Data Analysis eXpressions
5. (03:36) Introduction to Iterator Functions
6. (04:51) Learn how Iterators Work using SUMX & AVERAGEX Iterating Over Fact Table at Transaction Grain
7. (08:24) Be Careful of Context Transition & Iterating over a Fact Table that “Materialize” Unnecessary Tables.
8. (11:14) DAX Studio to Time DAX Formulas. Including how to use the ROW DAX Function to help time Measures.
9. (18:55) Create PivotTable with Measures and see that the Measure that has to materialize a Fact Table for each row in the iteration takes a LONG time to calculate in a PivotTable.
10. (19:49) Power BI Example of “Double Count” Problem with Context Transition. Be Careful of Context Transition & “Double Count” Problem. Solutions #1: Use Formula rather than Measure.
11. (25:07) Solution #2: Use Power Query to add Primary Key can fix the problem also.
12. (26:11) AVERAGEX at Day Grain use dDate Table. We want Context Transition in this formula and we will Never have the “Double Count” problem because dDate Table has no duplicates.
13. (27:37) Grain of Fact Table or Iterator.
14. (30:18) AVERAGEX at Month Grain with VALUES(Column)
15. (30:18) Introduction to idea that we need DAX Table functions like VALUES to help create the correct Grain for Tables that we can use in Iterator Functions.
16. (31:35) VALUES DAX Function
17. (33:17) DAX Studio to Visualize or Materialize Tables
18. (33:48) CROSSJOIN DAX Function.
19. (35:05) CONCATENATEX and VALUES to list values in the Current Filter Context
20. (40:15) AVERAGEX at Month Grain with CROSSJOIN(VALUES(Column),VALUES(Column))
21. (41:30) Can we reduce “Cardinality”? Try not to Iterate over Fact Table. Alternative Formula for Total Revenue in Power BI Example. Timing formulas in DAX Studio.
22. (48:20) Time DAX Measures from Power BI with DAX Studio.
23. (50:20) Closer look at ALL and VALUES DAX Functions. Discuss the Blank Row that shows up from unmatched item in a relationship.
24. (55:21) Compare and Contrast ALL and VALUES.
25. (56:10) DISTINCT and ALLNOBLANKROW DAX Functions.
26. (57:23) Look at other DAX Table Functions.
27. (58:15) Discussion of FILTER DAX Table Function and CALCULATETABLE DAX Table Function
28. (01:01:00) Look at ADDCOLUMNS DAX Function.
29. (01:01:30) Excel Existing Connections to pull data from Data Model into Excel Sheet.
30. (01:02:54) DAX VALUES Function to pull a variable from an Excel Sheet into the Data Model.
31. (01:05:05) Summary

View on YouTube

Download Start files:
http://bit.ly/2SnL4fe
Zipped Folder with data that was already loaded into previous file: http://bit.ly/2Ax85FJ
http://bit.ly/2SnL5ji
http://bit.ly/2AnzlpP
http://bit.ly/2SnL5Qk
Zipped Folder with data that was already loaded into previous file: http://bit.ly/2AkF9Aw
Download Finished files:
http://bit.ly/2StC4oX
http://bit.ly/2ApVN1D
http://bit.ly/2SnL6nm

pdf Notes: http://bit.ly/2Aox3GY
Assigned Homework:
Download Word Document and read: http://bit.ly/2SnL7rq
Then download the rest of the files and complete the homework:
http://bit.ly/2Ah6cN9
Examples of Finished Homework:
http://bit.ly/2SnKWfK
http://bit.ly/2Ah6eof

This video teaches you about the power and pitfalls of DAX Iterator Functions and DAX Table Functions in Excel Power Pivot and in Power BI.
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. (00:31) Summary of what we know so far in class about Iterators and Table Functions
3. (01:38) Summary of what we will learn in this video about Iterators and Table Functions
4. (02:51) What does DAX stand for? Data Analysis eXpressions
5. (03:36) Introduction to Iterator Functions
6. (04:51) Learn how Iterators Work using SUMX & AVERAGEX Iterating Over Fact Table at Transaction Grain
7. (08:24) Be Careful of Context Transition & Iterating over a Fact Table that “Materialize” Unnecessary Tables.
8. (11:14) DAX Studio to Time DAX Formulas. Including how to use the ROW DAX Function to help time Measures.
9. (18:55) Create PivotTable with Measures and see that the Measure that has to materialize a Fact Table for each row in the iteration takes a LONG time to calculate in a PivotTable.
10. (19:49) Power BI Example of “Double Count” Problem with Context Transition. Be Careful of Context Transition & “Double Count” Problem. Solutions #1: Use Formula rather than Measure.
11. (25:07) Solution #2: Use Power Query to add Primary Key can fix the problem also.
12. (26:11) AVERAGEX at Day Grain use dDate Table. We want Context Transition in this formula and we will Never have the “Double Count” problem because dDate Table has no duplicates.
13. (27:37) Grain of Fact Table or Iterator.
14. (30:18) AVERAGEX at Month Grain with VALUES(Column)
15. (30:18) Introduction to idea that we need DAX Table functions like VALUES to help create the correct Grain for Tables that we can use in Iterator Functions.
16. (31:35) VALUES DAX Function
17. (33:17) DAX Studio to Visualize or Materialize Tables
18. (33:48) CROSSJOIN DAX Function.
19. (35:05) CONCATENATEX and VALUES to list values in the Current Filter Context
20. (40:15) AVERAGEX at Month Grain with CROSSJOIN(VALUES(Column),VALUES(Column))
21. (41:30) Can we reduce “Cardinality”? Try not to Iterate over Fact Table. Alternative Formula for Total Revenue in Power BI Example. Timing formulas in DAX Studio.
22. (48:20) Time DAX Measures from Power BI with DAX Studio.
23. (50:20) Closer look at ALL and VALUES DAX Functions. Discuss the Blank Row that shows up from unmatched item in a relationship.
24. (55:21) Compare and Contrast ALL and VALUES.
25. (56:10) DISTINCT and ALLNOBLANKROW DAX Functions.
26. (57:23) Look at other DAX Table Functions.
27. (58:15) Discussion of FILTER DAX Table Function and CALCULATETABLE DAX Table Function
28. (01:01:00) Look at ADDCOLUMNS DAX Function.
29. (01:01:30) Excel Existing Connections to pull data from Data Model into Excel Sheet.
30. (01:02:54) DAX VALUES Function to pull a variable from an Excel Sheet into the Data Model.
31. (01:05:05) Summary

View on YouTube