Data Analysis Expressions in Power Pivot for Excel

Data Analysis Expressions in Power. Pivot for Excel 2010 Howie Dickerman Microsoft Corporation

Agenda - Data Analysis Expressions (DAX) • • Power. Pivot for Excel and why we need DAX Demo(s) of Data Analysis Expressions How (and where) to use DAX Functions by Category • Excel Functions • Relationships • Aggregation • Table Functions • Time Intelligence

Power. Pivot Add-In for Excel 2010 • Power. Pivot for Excel Self-Service BI • Pivot. Table(s) based on multiple tables of data • Ease-of-use: No need to learn multi-dimensional concepts or specialized languages like MDX • Data Analysis Expressions (DAX) • Simple Excel style formulas • Define new fields in the Pivot. Table field list • Enable Excel users to perform powerful data analysis using the skills they already have

Demo 1 Power. Pivot Add-In • Power. Pivot for Excel 2010 • • • Sample Data Power. Pivot ribbon in Excel Power. Pivot window Sales [Amount] = [Qty]*[Price] Field List with multiple tables Sales Pivot. Tables by customer/product

Data Analysis Expressions (DAX) • DAX uses Excel syntax and many Excel functions • No notion of addressing individual cells or ranges • DAX functions refer to columns in the data • DAX is not a replacement for MDX Sample DAX expression Comment = [First Name] &“ ”& [Last Name] String concatenation just like Excel SUM function takes a column =SUM(Sales[Amount]) name instead of a range of cells new RELATED function follows =RELATED (Product[Cost]) relationship between tables 5

Beyond Excel – Relational data • Power. Pivot for Excel works with multiple tables of related data, so DAX provides functions that implement relational database concepts: • Filter Tables • Aggregate over Tables • Follow Relationships (fetch data from a related table) • Many-to-One • One-to-Many

Demo 2 - RELATED and RELATEDTABLE • RELATED, RELATEDTABLE in calc columns: • Sales [Unit. Cost] = RELATED (Product[Cost]) • Sales [Total. Cost] = [Qty]*[Unit. Cost] • Calc column in 3 other tables fetches related transactions and adds up amounts: • [Sales]=SUMX(RELATEDTABLE(Sales), Sales[Amount])

Beyond Excel – Dynamic Aggregation • DAX has functions to assist with dynamic aggregation of measures in Pivot. Tables • Discovery of Filter Context • (e. g. What is current product or year? ) • Modification of Filter Context • (e. g. switch to all products or years) • Time Intelligence Functions • (e. g. compare values to previous period)

Demo 3 – Examine context with VALUES • Demo VALUES in Sales Pivot. Table (Product) • Concept of Filter Context - one formula in many cells • Demo auto-complete while entering formulas • = Values(Time[Year]) & “-” &Values (Product[Name]) • = IF (Values(Time[Year])=2007, “Baseline”, “Other”)

Data Analysis Expressions are used in two places: Calculated Columns and Measures • Calculated Columns • Expression (formula) is evaluated for each row in table • Column in table is fully populated with values • Values may be placed on Pivot Axis or aggregated and then placed in Values area (as a measure) • Measures • Named expression (formula) is not evaluated until placed into Values area of a Pivot. Table • Measure is evaluated for each Pivot. Table cell using the “filter context” for that cell (row & column headers) • Some measure calculations cannot be accomplished by aggregating a calculated column (e. g. ratios don’t add) 10

Demo 4 – Creating Measures • Row & Column Labels • Checkbox or drag/drop places values on axis • Values Area • SUM(numeric) or COUNT(non-numeric) • Two ways to author measures • Choose column from table (and choose agg) • Enter custom DAX expression (formula) • Each measure evaluated w/distinct context for each Pivot. Table cell

Functions for following relationships • Related (Column) • Follow existing many-to-one relationship and fetch the value from the specified column in the related table • Related. Table (Table) • Follow existing relationship (either direction) and return table containing matching row(s) from the specified table • Note that this returns a table, so must be used as a parameter to another function Ex: = SUMX ( Related. Table(Sales), Sales[Amount]) • Excel doesn’t offer table functions, but DAX needs them for intermediate results. (List of table functions to be shown later. ) 12

DAX Aggregation Functions • DAX implements aggregation functions from Excel including SUM, AVERAGE, MIN, MAX, COUNT, but instead of taking multiple arguments (a list of ranges, ) they take a reference to a column • DAX also adds some new aggregation functions which aggregate any expression over the rows of a table • • • SUMX (Table, Expression) AVERAGEX (Table, Expression) COUNTAX (Table, Expression) MINX (Table, Expression) MAXX (Table, Expression) 13

More than 80 Excel Functions in DAX Date and Time DATEVALUE DAY EDATE EOMONTH HOUR MINUTE MONTH NOW SECOND TIMEVALUE TODAY WEEKNUM YEARFRAC Information ISBLANK ISERROR ISLOGICAL ISNONTEXT ISNUMBER ISTEXT Logical AND IF IFERROR NOT OR FALSE TRUE Math and Trig Statistical ABS AVERAGE CEILING, ISO. CEILING AVERAGEA EXP COUNT FACT COUNTA FLOOR COUNTBLANK INT MAX LN MAXA LOG MIN LOG 10 MINA MOD MROUND PI POWER QUOTIENT RANDBETWEEN Text CONCATENATE EXACT FIND FIXED LEFT LEN LOWER MID REPLACE REPT RIGHT SEARCH SUBSTITUTE TRIM UPPER VALUE ROUNDDOWN ROUNDUP SIGN SQRT SUMSQ TRUNC 14

DAX Table Functions • Related. Table (Table) • returns table containing related rows of data • Filter (Table, Condition) • Returns table filtered to include rows where condition is true • Distinct (Column) • Returns one column table containing the distinct (unique) values in a column • Values (Column) • Returns one column table containing valid values in a column in current filter context • All (Table), All(Column) • Returns all data for the specified object, ignoring context filters and without duplicates • All. Except (Table, Col 1, Col 2, . . . ) • Returns all of the data for the specified table, ignoring any context filters EXCEPT that filters for the specified columns are retained 15

Changing Context and Calculating formulas in the modified context • We provide an entire category of functions which are designed to modify the context and then perform calculations in the modified context. • These are the CALCULATE functions and ALL functions which have several variants depending on how we want to modify the context of a calculation. • The use of a measure as if it were a function is a useful shorthand notation for the CALCULATE function. =[Sales. Amt]/[Sales. Amt](All(Product)) 16

Demo 5 – DAX measures • Sales Table • [Sales. Amt] = SUM (Sales [Amount]) • [All. Prod] = CALCULATE ([Sales. Amt], All(Product)) = [Sales. Amt] (All(Product) • [Cost] = SUM (Sales[Total. Cost]) • [Margin] = Sales[Sales. Amt] – Sales[Cost] • [Margin%] = (Sales[Sales. Amt] – Sales[Cost]) / [Sales]

35 Time Intelligence Functions • Manipulating the time period is a very common requirement for Business Intelligence analysis. • Time Intelligence requires a Date column in the data and navigates using that column • Time Intelligence functions work with intervals of days, months, quarters, and years, and include: • Functions that return a single date • Functions that return a table of dates • Functions that evaluate expressions over time period 18

Functions that return a single date 1. 2. 3. 4. First. Date (Date_Column) Last. Date (Date_Column) First. Non. Blank (Date_Column, Expression) Last. Non. Blank (Date_Column, Expression) 5. Startof. Month (Date_Column) 6. Startof. Quarter (Date_Column) 7. Startof. Year (Date_Column [, YE_Date]) 8. Endof. Month (Date_Column) 9. Endof. Quarter (Date_Column) 10. Endof. Year (Date_Column [, YE_Date])

Functions that return a table of dates 11. 12. 13. 14. Date. Add (Date_Column, Number_of_Intervals, Interval) Dates. Between (Date_Column, Start_Date, End_Date) Dates. In. Period (Date_Column, Start_Date, Number_of_Intervals, Interval) Parallel. Period (Date_Column, Number_of_Intervals, Interval) 15. Previous. Day (Date_Column) 16. Previous. Month (Date_Column) 17. Previous. Quarter (Date_Column) 18. Previous. Year (Date_Column [, YE_Date]) 19. Next. Day (Date_Column) 20. Next. Month (Date_Column) 21. Next. Quarter (Date_Column) 22. Next. Year (Date_Column [, YE_Date]) 23. Dates. MTD (Date_Column) 24. Dates. QTD (Date_Column) 25. Dates. YTD (Date_Column [, YE_Date]) 26. Same. Period. Last. Year (Date_Column)

Functions that evaluate expressions over a time period 27. Total. MTD (Expression, Date_Column [, Set. Filter]) 28. Total. QTD (Expression, Date_Column [, Set. Filter]) 29. Total. YTD (Expression, Date_Column [, Set. Filter] [, YE_Date]) 30. Opening. Balance. Month (Expression, Date_Column [, Set. Filter]) 31. Opening. Balance. Quarter (Expression, Date_Column [, Set. Filter]) 32. Opening. Balance. Year (Expression, Date_Column [, Set. Filter] [, YE_Date]) 33. Closing. Balance. Month (Expression, Date_Column [, Set. Filter]) 34. Closing. Balance. Quarter (Expression, Date_Column [, Set. Filter]) 35. Closing. Balance. Year (Expression, Date_Column [, Set. Filter] [, YE_Date])

Demo 6 - Time Intelligence • Sales Table [YOYGrowth] = [Sales. Amt] – [Sales. Amt](Date. Add(Time[Date], -1, Year)) [QTDSales] = Total. QTD ([Sales. Amt], Time[Date]) [Year. Ago] = [Sales. Amt] (Parallel. Period(Time[Date], -12, Month)) CTP Note: In the CTP 3 release, it is necessary to add All(Time) as an additional filter to the Time Intelligence invocations so the time selection won’t be restricted by the current filter context.

Summary • DAX formulas use familiar Excel syntax: • Calculated Columns (in Power. Pivot tables) • Measures (in Pivot. Table UI) • Functionality includes: • • • Excel functions (math, statistical, date/time, text, etc. ) Aggregation of any expression over a table Table functions (Filter(), Related. Table(), Distinct(), etc. ) Fetch data across relationships (one-many, many-one) Set or modify calculation context (All, Calculate, etc. ) Time Intelligence functions 23

Call To Action / Resources Visit website: http: //Power. Pivot. com Download these two applications and try out DAX! Microsoft Office 2010 (Beta) SQL Server Power. Pivot for Excel 2010 (CTP 3 ) Product Team Blog Site: http: //blogs. msdn. com/powerpivot DAX Questions? e-mail: howied@microsoft. com 24

Complete the Evaluation Form & Win! • You could win a Dell Mini Netbook – every day – just for handing in your completed form! Each session form is another chance to win! Pick up your Evaluation Form: • Within each presentation room • At the PASS Booth near registration area Drop off your completed Form: • Near the exit of each presentation room • At the PASS Booth near registration area Sponsored by Dell

PASS Power. Pivot Sweepstakes 1) Become a follower of www. twitter. com/powerpivot 2) Re-tweet our message: “Want to learn more, go to www. powerpivot. com and sign up for CTP #powerpivot” 3) You are entered to win an XBOX 360 4) We will randomly select a follower who re-tweeted this and announce the winner 5) The results will be posted on Monday, November 9 th at www. twitter. com/powerpivot *No Purchase Necessary. Open only to residents 18+ of the 50 US (includes D of C) or Canada (excluding Quebec). Game ends 11/5/09. For full Official Rules, visit www. powerpivot. com/contest

Thank you for attending this session and the 2009 PASS Summit in Seattle