Deprecated: Creation of dynamic property CF\WordPress\DataStore::$logger is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php on line 23
Deprecated: Creation of dynamic property CF\WordPress\Proxy::$pluginAPI is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/Proxy.php on line 31
Deprecated: file_get_contents(): Passing null to parameter #2 ($use_include_path) of type bool is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/codelights-shortcodes-and-widgets/codelights.php on line 20
Warning: session_start(): Session cannot be started after headers have already been sent in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/automatic-video-posts/vendor/ternstyle/wordpress/src/class/wordpress.php on line 53
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Deprecated: Creation of dynamic property ternplugin\youtube_video::$post is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/automatic-video-posts/vendor/ternstyle/plugin/src/class/youtube_video.php on line 64
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
{"id":960,"date":"2018-12-30T23:04:00","date_gmt":"2018-12-31T06:04:00","guid":{"rendered":"https:\/\/bonbonsguide.com\/wp\/?p=960"},"modified":"2018-12-30T23:04:00","modified_gmt":"2018-12-31T06:04:00","slug":"msptda-18-dax-iterators-table-functions-grain-cardinality-materializing-tables-excel-power-bi-5","status":"publish","type":"post","link":"https:\/\/bonbonsguide.com\/wp\/blog\/2018\/12\/msptda-18-dax-iterators-table-functions-grain-cardinality-materializing-tables-excel-power-bi-5\/","title":{"rendered":"MSPTDA 18: DAX Iterators, Table Functions, Grain, Cardinality, Materializing Tables Excel & Power BI"},"content":{"rendered":"Download Start files:
\n http:\/\/bit.ly\/2SnL4fe
\nZipped Folder with data that was already loaded into previous file: http:\/\/bit.ly\/2Ax85FJ
\nDownload Other Start Files: http:\/\/bit.ly\/2SnL5ji
\n http:\/\/bit.ly\/2AnzlpP
\n http:\/\/bit.ly\/2SnL5Qk
\nZipped Folder with data that was already loaded into previous file: http:\/\/bit.ly\/2AkF9Aw
\nDownload Finished files:
\n http:\/\/bit.ly\/2StC4oX
\n http:\/\/bit.ly\/2ApVN1D
\n http:\/\/bit.ly\/2SnL6nm<\/p>\n
pdf Notes: http:\/\/bit.ly\/2Aox3GY
\nAssigned Homework:
\n Download Word Document and read: http:\/\/bit.ly\/2SnL7rq
\n Then download the rest of the files and complete the homework:
\n http:\/\/bit.ly\/2Ah6cN9
\n Examples of Finished Homework:
\n http:\/\/bit.ly\/2SnKWfK
\n http:\/\/bit.ly\/2Ah6eof<\/p>\n
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.
\nComprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.<\/p>\n
Topics:
\n1. (00:15) Introduction
\n2. (00:31) Summary of what we know so far in class about Iterators and Table Functions
\n3. (01:38) Summary of what we will learn in this video about Iterators and Table Functions
\n4. (02:51) What does DAX stand for? Data Analysis eXpressions
\n5. (03:36) Introduction to Iterator Functions
\n6. (04:51) Learn how Iterators Work using SUMX & AVERAGEX Iterating Over Fact Table at Transaction Grain
\n7. (08:24) Be Careful of Context Transition & Iterating over a Fact Table that “Materialize” Unnecessary Tables.
\n8. (11:14) DAX Studio to Time DAX Formulas. Including how to use the ROW DAX Function to help time Measures.
\n9. (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.
\n10. (19:49) Power BI Example of \u201cDouble Count\u201d Problem with Context Transition. Be Careful of Context Transition & “Double Count” Problem. Solutions #1: Use Formula rather than Measure.
\n11. (25:07) Solution #2: Use Power Query to add Primary Key can fix the problem also.
\n12. (26:11) AVERAGEX at Day Grain use dDate Table. We want Context Transition in this formula and we will Never have the \u201cDouble Count\u201d problem because dDate Table has no duplicates.
\n13. (27:37) Grain of Fact Table or Iterator.
\n14. (30:18) AVERAGEX at Month Grain with VALUES(Column)
\n15. (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.
\n16. (31:35) VALUES DAX Function
\n17. (33:17) DAX Studio to Visualize or Materialize Tables
\n18. (33:48) CROSSJOIN DAX Function.
\n19. (35:05) CONCATENATEX and VALUES to list values in the Current Filter Context
\n20. (40:15) AVERAGEX at Month Grain with CROSSJOIN(VALUES(Column),VALUES(Column))
\n21. (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.
\n22. (48:20) Time DAX Measures from Power BI with DAX Studio.
\n23. (50:20) Closer look at ALL and VALUES DAX Functions. Discuss the Blank Row that shows up from unmatched item in a relationship.
\n24. (55:21) Compare and Contrast ALL and VALUES.
\n25. (56:10) DISTINCT and ALLNOBLANKROW DAX Functions.
\n26. (57:23) Look at other DAX Table Functions.
\n27. (58:15) Discussion of FILTER DAX Table Function and CALCULATETABLE DAX Table Function
\n28. (01:01:00) Look at ADDCOLUMNS DAX Function.
\n29. (01:01:30) Excel Existing Connections to pull data from Data Model into Excel Sheet.
\n30. (01:02:54) DAX VALUES Function to pull a variable from an Excel Sheet into the Data Model.
\n31. (01:05:05) Summary<\/p>\n