Tag: How to

MSPTDA 18: DAX Iterators, Table Functions, Grain, Cardinality, Materializing Tables Excel & Power BI

Posted on by Annie D

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

MSPTDA 18: DAX Iterators, Table Functions, Grain, Cardinality, Materializing Tables Excel & Power BI

Posted on by Annie D

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

Count Number Products Battle: Excel? DAX? Power Query? Office 365? Excel Magic Trick 1533

Posted on by Annie D

Download Excel Start Files: http://bit.ly/2Q2FzRe
Download Excel Finished Files: http://bit.ly/2V9SZP9

In this video compare five methods to count how many products are in a column.
Topic:
(00:05) Introduction
1. (01:25) Excel Spreadsheet Function: COUNTIFS
2. (03:23) Excel Standard PivotTable
3. (04:47) Power Query: Group By feature
4. (07:23) DAX Functional Language of Excel Power Pivot & Power BI DAX: COUNTROWS
5. (11:08) Office 365 Dynamic Array Functions: UNIQUE & COUNTIFS
(14:40) Summary
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs
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

MSPTDA 17: Power BI Online: Dashboards, Reports, Excel Workbooks, Data Sets.

Posted on by Annie D

Download Power BI Desktop “EMT 1366” FINISHED File: https://ift.tt/2QC1UtU
Download Power BI Desktop “MSPTDA Video #16 Power BI File” FINISHED File: https://ift.tt/2C070XR
Download Excel File from “MSPTDA Video #15 Power Pivot File” FINISHED File: https://ift.tt/2QCa718
Download pdf Notes about video 16 & 17: https://ift.tt/2BrFbGE
NO HOMEWORK for this video.
This video teaches about Power BI Online. How to upload Excel Files. Create Online Dashboard.

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:00) Logging in to Power BI Web Site to view reports.
3. (01:48) Introduction to Dashboards, Reports, Workbooks and Data Sets.
4. (02:48) Create Workspace.
5. (03:19) Upload Power BI Report Files.
6. (03:45) Upload Excel Power Pivot Workbook File. Two Methods: 1) Upload Workbook (to see sheets), 2) Upload Workbook Data (upload Data Model from Excel).
7. (04:55) View Excel File Worksheets in Excel Online at powerbi.com web site.
8. (05:17) Look at the four sections (Dashboards, Reports, Workbooks and Data Sets) after we created a Workspace and uploaded items.
9. (05:43) Create Online Dashboard in Power BI Online.
10. (07:01) Create Power BI Report Based on the Data Model in a Published Excel File.
11. (07:56) Summary for Dashboards, Reports, Workbooks and Data Sets

View on YouTube

VLOOKUP To Get Complete Record: ROWS, COLUMNS or SEQUENCE Function? EMT 1532

Posted on by Annie D

Download Excel File: https://ift.tt/2UK2Evz
In this video see how to use VLOOKUP to retrieve an entire record using three methods: 1) COLUMNS, 2) ROWS, 3) SEQUENCE.

1. (00:05) Introduction
2. (00:35) Do we show record horizontally, or vertically? Discussion of sequential numbers that represent the column numbers needed by the VLOOKUP Function.
3. (01:33) COLUMNS & VLOOKUP
4. (03:51) ROWS & VLOOKUP
5. (04:48) SEQUENCE & VLOOKUP (requires that you have Office 365)
6. (07:57) Summary
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs

View on YouTube

rank in Access

MSPTDA 16: Power BI Desktop Comprehensive Introduction: Power Query, DAX, Dashboards, Publishing

Posted on by Annie D

Download Zipped Folder with Text Files & Excel File: https://ift.tt/2L8w0is
Download Power BI Desktop FINISHED File: https://ift.tt/2C070XR
Download pdf Notes about Power Query: https://ift.tt/2L657Mh

This video is a comprehensive lesson in Power BI Desktop: Power Query to import data, DAX Formulas and Relationships to complete Data Model, Creating Dashboards, Publishing and Sharing Reports.

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 of what we will do in this video.
2. (02:25) Overview of Excel Power Pivot & Power BI Desktop
3. (02:44) Approximate History of Power BI Desktop :
4. (03:15) Different Versions of Power BI (Different Power BI Products) Available from Microsoft
5. (04:56)Download Power BI Desktop (link to Avi’s video: https://www.youtube.com/watch?v=5Fv-I9xQkcc)
6. (05:43) List of Charts and Visualizations for your Dashboard (Review from prerequisite classes Busn 216 & 218)
7. (06:02) Overriding Steps for our Project
8. (06:27) Open a blank Power BI File
9. (07:04) Introduction to Power BI Window and User Interface
10. (08:32) Power Query to Import Multiple CSV Files and Clean and Transform Data
11. (13:38) Why we do NOT use Number or Date Fields from a Fact Table
12. (15:57) Import Dimension Tables from a Single Excel File
13. (18:09) Merge Snow Flake Dimension Tables into dProduct Table
14. (19:30) Do NOT import to Data Model (Uncheck Enable Load)
15. (20:22) Old Relationship View & New Relationships View with Properties & Better Selection Capability
16. (20:41) Steps to create Date Table using CALENDAR DAX Table Function & Calculated Columns. See many DAX Functions such as CALENDAR, FORMAT and others.
17. (16:10) Sort By Column to get Months to Sort correctly.
18. (27:47) Create Fiscal Periods for Data Table, including Helper Column for Sorting Fiscal Period correctly.
19. (33:12) Hide Columns from Report View
20. (34:00) Create DAX Measures and see why we do not use Implicit Measures.
21. (36:17) SUMX DAX Function
22. (38:15) Row Context (how formula calculates for each row in a table or Iterator Function)
23. (40:12) Filter Context (How Measures Calculate and how Tables are Filtered when Measures Calculate)
24. (41:50) Measure for Average Daily Revenue. Learn about Context Transition. See AVERAGEX Function to iterate at the Daily level.
25. (47:55) Conventions for DAX Formulas with a great tip from Marco Russo and Albetro Ferrari
26. (49:00) More About Filter Context and Context Transition
27. (49:26) Gross Profit Measures
28. (51:48) Refine Data Model in Power Query by Removing Columns in dProduct Table
29. (52:40) Learn about how to Create & Format Visualizations
30. (52:40) Create “Ave Daily GP” Dashboard.
31. (52:40) Create Matrix and add Conditional Formatting
32. (55:29) Create Column Chart and add Conditional Formatting
33. (56:00) Hierarchies
34. (56:52) Drill Down Icons in Power BI
35. (59:09) Create Line Chart
36. (01:00:00) Create Card
37. (01:01:00) Edit Interactions between visualizations
38. (01:02:50) Create “Fiscal Report” Dashboard
39. (01:05:32) Bookmark to save views of a Dashboard
40. (01:06:20) Create “Ave Last 12 Months” Dashboard
41. (01:06:37) DAX Measure for Average Transactional Revenue. See AVERAGEX Function to iterate at the transaction line item level.
42. (01:07:30) Visual of how we change the Filter Context to get dates for a full year backwards.
43. (01:08:25) CALCULATE & DATESINPERID & LASTDATE DAX Functions to calculate Measure for Rolling 12 Month Average for Transaction Level Data.
44. (01:12:08) Create “Question” Dashboard. Learn about Ask A Question feature.
45. (01:13:08) Publish Report to powerbi.com
46. (01:14:15) Edit at powerbi.com
47. (01:14:34) Publish to Web with Free Power BI Desktop version and allow public to review Report
48. (01:16:15)Publish and Share with Power BI Pro Account
49. (01:17:44) Source Data Changes and Refresh
50. (01:18:18) Summary

View on YouTube

Comprehensive Introduction to Excel Power Pivot, DAX Formulas and DAX Functions

Posted on by Annie D

Download Excel START File: https://ift.tt/2FrxeX5
Second Excel Start File: https://ift.tt/2Dtf4Sf
Download Zipped Folder with Text Files: https://ift.tt/2Frxfu7
Download Excel FINISHED File: https://ift.tt/2qSnYkx
Download pdf Notes about Power Query: https://ift.tt/2FrxwgD
Assigned Homework:
Download Excel File with Instructions for Homework: https://ift.tt/2qRom2T
Examples of Finished Homework: https://ift.tt/2Frxgyb

This video teaches everything you need to know about Power Pivot, Data Modeling and building DAX Formulas, including all the gotchas that most Introductory videos do not teach you!!!

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

Topics:
(00:15) Introduction & Overview of Topics in Two Hour Video
1. (04:36) Standard PivotTable or Data Model PivotTable?
2. (05:51) Excel Power Pivot & Power BI Desktop?
3. (12:31) Power Query to Extract, Transform and Load Data to Data Model – Get data from Text Files, Relational Database and Excel File.
4. (25:47) Build Relationships
5. (27:43) Introduction to DAX Formulas: Measures & Calculated Columns
6. (29:15) DAX Calculated Column using the DAX Functions, RELATED and ROUND
7. (31:20) Row Context: How DAX Calculated Columns are Calculated: Row Context
8. (33:49) We do not want to use Calculated Column results in PivotTable using Implicit Measures
9. (34:05) DAX Measure to add results from Calculated Column, using DAX SUM Function.
10. (35:29) Number Formatting for DAX Measures
11. (36:35) Data Model PivotTable
12. (39:31) Explicit DAX Formulas rather than Implicit DAX Formulas
13. (41:50) Show Implicit Measures
14. (45:00) Filter Context (First Look) How DAX Measures are Calculated
15. (50:14) Drag Columns from Fact Table or Dimension Table?
16. (53:30) Hiding Columns and Tables from Client Tool
17. (55:52) Use Power Query to Refine Data Model
18. (57:54) SUMX Function (Iterator Function). DAX Measure for Revenue using the SUMX Function to simulate Calculated Columns in DAX Measures
19. (01:01:00) Compare and Contrast Calculated Columns & Measures
20. (01:04:26) Why We Need a Date Table. Why we do NOT use the Automatic Grouping Feature for a Data Model PivotTable
21. (01:06:46) Build an Automatic Date Table in Excel Power Pivot. And then build Relationship.
22. (01:11:00) Introduction to Time Intelligence DAX Functions. See TOTALYTD DAX Function
23. (01:13:47) Introduction to CALCULATE Function: Function that can “see” Data Model and can change the Filter Context. (01:18:00) Also see the ALL and DIVIDE DAX Functions. Create formula for “% of Grand Total”. Also learn about (01:21:30) Context Transition and the Hidden CALCULATE on all Measures.
24. (01:27:18) DAX Formula Benefits.
25. (01:28:00) Example of DAX Formula that is easier to author than if we tried to do it with a Standard Pivot Table or Array Formulas
26. (01:31:50) AVERAGEX Function (Iterator Function) to calculate Average Daily Revenue.
27. (01:34:00) Filter Context (Second Look) AVERAGEX Iterator Function
28. (01:36:16) Build Dashboard. Create multiple DAX Formulas. Create Multiple Data Model PivotTables and a Data Model Chart.
29. (01:38:38) Create Measures for Gross Profit and Gross Profit %
30. (01:41:27) Continue making more Data Model PivotTables.
31. (01:41:50) Make Data Model Pivot Chart.
32. (01:45:10) Conditional Formatting for Data Model PivotTable.
33. (01:46:22) DAX Text Formula for title of Dashboard
34. (01:47:50) CUBE Function to Convert Data Model PivotTable to Excel Spreadsheet Formulas.
35. (01:50:05) Adding New Data and Refreshing.
36. (01:50:40) Update Excel Power Pivot Automatic Date (Calendar) Table. Clue is the blank in the Dimension Table Filter.
37. (01:52:20) How to Double Check that a DAX Formula is yielding the correct answer?
38. (01:53:22) DAX Table Functions. See CALCULATETABLE DAX Function.
39. (01:55:07) DAX Studio to visualize DAX Table Functions, and to efficiently create DAX Formulas
40. (02:00:12) Existing Connections to import data from Data Model into an Excel Sheet
(02:03:15) Summary

View on YouTube

rank in Access

Excel Dynamic Array: Column of Records into Proper Data Set (Excel Magic Trick 1531)

Posted on by Annie D

Download Excel File: https://ift.tt/2zEgCVN
In this video see how to use Excel Dynamic Array Formulas to convert a column of records into a Proper Data Set. This is a great trick from Bill Szysz.
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
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

Excel Dynamic Arrays: Accounting: Dynamic Schedule of Accounts (Excel Magic Trick 1530)

Posted on by Annie D

Download Excel File: https://ift.tt/2zEgCVN
In this video learn how to create a dynamic Schedule of Accounts based on an Expenses Category Formula Inpout. When we change the Expenses Category, a new report appears.
In this video see these Excel Features:
SORT Array Function
UNIQUE Array Function
Data Validation List Dropdown
FILTER Array Function
SEQUENCE Array Function
COUNTIFS Function
IF Function
ROWS Function
SUMIFS Function
Conditional Formatting
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
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

Excel Dynamic Arrays: FILTER Array Function AND & OR Logical Tests (Excel Magic Trick 1529)

Posted on by Annie D

Download Excel File: https://ift.tt/2POKoBi
This video teaches all about AND Logical tests and OR Logical Test and the intricacies of how to construct and use them in Excel, including single cell logical Test sand Array Logical tests. It also examines how Excel Functions and Features interprets FALSE and 0 (zero) as FALSE and TRUE and Any Non-Zero Number as a TRUE. Then we take the lessons of Logical tests and Apply them in Various ways in the FILTER ARRAY Function.
We also get to see a great trick from Peter Bartholomew about how to create a shorter formula than the one as seen in Excel Magic Trick 1521.
We also entertain a question from John Borg about an OR Logical Test inside the FILTER Function.
Reference video about FILTER Function EMT 1521: https://www.youtube.com/watch?v=5zRab2Grz7Q
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
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): https://www.youtube.com/watch?v=7jJMDGZpjSk

View on YouTube

Excel Dynamic Arrays & Charts: Spilled Arrays in Charts with Defined Names (Excel Magic Trick 1527)

Posted on by Annie D

Download Excel File: https://ift.tt/2QfQuHY
This video discusses how to get Spilled Arrays into Charts. Direct Spilled Arrays don’t seem to work, but we can use the Old Fashion Method and use Defined Names to point to our Spilled Arrays..
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs

Excel Dynamic Arrays: Fully Dynamic Cross Tabulated Reports? Unbelievable! EMT 1520 : https://www.youtube.com/watch?v=ZXn0D4Ch7zg

View on YouTube

Excel Dynamic Arrays: Cross Tabulated Report, Totals Top & Left MMULT Array Function (EMT 1526)

Posted on by Annie D

Download Excel File: https://ift.tt/2QfQuHY
This video is a follow up to EMT 1520. In this video we see how to create fully Dynamic Cross Tabulated Report driven by formula inputs, and have totals at the Left and Above using the MMULT Array Function.
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
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)
Related Videos:
Original video about Cross Tab Report, EMT 1520: https://www.youtube.com/watch?v=ZXn0D4Ch7zg
Cross Tab Report with totals at bottom and Right, EMT 1528: https://www.youtube.com/watch?v=wHeXjYQGvX8
MMULT Array Function Video: https://www.youtube.com/watch?v=RyorWHIKPC0

View on YouTube