1 1075 455 etc Value used to increment between Start End value must be non zero

1 1075 455 etc value used to increment between start

This preview shows page 110 - 117 out of 178 pages.

-1, -10.75, -45.5, etc. Value used to increment between Start & End value (must be non-zero and positive ) Examples : .25, 1, 1.7745, 5, etc. Generating Data GENERATESERIES () Returns a one column table populated with sequential values PRO TIP: GENERATESERIES is a great way to build a custom range of data to be used as a parameter input
Image of page 110
TABLE CONSTRUCTOR *Copyright 2020, Excel Maven & Maven Analytics, LLC Single column with multiple rows : Single row with multiple columns : Multi-row , multi-column table: Generating Data The table constructor { } can be used to build tables containing one or more columns or rows, based on fixed values or DAX expressions which return scalar values NOTE: Column headers can’t be defined using table constructor syntax 1 2 3
Image of page 111
*Copyright 2020, Excel Maven & Maven Analytics, LLC Mark and I are working on some forecasts, and I need a new table showing target sales for April 2019, by product group. Below is a screen shot of what I’m looking for -- thank you! Jean LeBean (CEO) 3:15 PM CHANNELS (12) MESSAGES (23) Jean LeBean Mark Brewer 1 MAVEN ROASTERS KEY OBJECTIVES: 1. Use DATATABLE to recreate the table that Jean needs for her forecasts ASSIGNMENT: GENERATING DATA 1
Image of page 112
CALCULATED TABLE JOINS *Copyright 2020, Excel Maven & Maven Analytics, LLC
Image of page 113
Calculated table joins are used to combine two or more tables of data; common examples include CROSSJOIN , UNION , EXCEPT and INTERSECT COMMON USE CASES: Blending or combining data across multiple tables Creating advanced calculations like new vs. returning users or repeat purchase behavior Querying tables to troubleshoot errors or better understand relationships in a data model TOPICS WE’LL COVER: CROSSJOIN EXCEPT UNION INTERSECT *Copyright 2020, Excel Maven & Maven Analytics, LLC CALCULATED TABLE JOINS
Image of page 114
CROSSJOIN *Copyright 2020, Excel Maven & Maven Analytics, LLC = CROSSJOIN ( Table , Table , […] ] ) Product Category Product Group Coffee Beverages Tea Beverages Bakery Food Chocolate Food Product Category Product Group Store ID Coffee Beverages 3 Tea Beverages 3 Bakery Food 3 Chocolate Food 3 Coffee Beverages 5 Tea Beverages 5 Bakery Food 5 Chocolate Food 5 Coffee Beverages 8 Tea Beverages 8 Bakery Food 8 Chocolate Food 8 Store ID 3 5 8 CROSSJOIN List of table expressions to include in the crossjoin Examples : ‘Product Lookup’ VALUES ( ‘Sales by Store’[ store_id)) CROSSJOIN Product of two sets, forming a new set containing all ordered pairs CROSSJOIN () Returns a table that contains the cartesian product of the specified tables IMPORTANT NOTES: Column names must all be different in all table arguments The # of rows returned equals the product of rows in all tables The # of columns returned equals the sum of columns in all tables Resulting table contains 12 rows (4*3) and 3 columns (2+1)
Image of page 115
UNION *Copyright 2020, Excel Maven & Maven Analytics, LLC = UNION ( Table , Table , […] ) Accepts any DAX expression for two (or more) tables with identical column structure Examples : ‘Sales Target 2019’, ‘Sales Target 2020’ ‘Sales Target 2019’, DATATABLE ( ) UNION stacks tables together, just like append !
Image of page 116
Image of page 117

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture