SSWUG vConf Build real BI applications with PowerPivot expressions
Build real BI applications with PowerPivot expressions [email protected]
Twitter: donalddotfarmer
Advanced Applications Excel • Limited by data size • No data model
Excel with PowerPivot • Unlimited data • Data model enables advanced calculations
The tool of choice? • Data size is not the only factor • Consider modelling, relationships and calcs
PowerPivot Data Relational sources Multidimensional Sources File Sources
Data Feeds Clipboard Excel
Getting data into PowerPivot
DEMO
PowerPivot Relationships • Discover – Only “perfect” relationships
• Create – With hints to help
• Manage – Browse, remove
Relationships in PowerPivot
DEMO
PivotTables and Slicers Easy-of-use • • • •
Well-understood Tables and Charts Useful formatting options Help from PowerPivot
Slicers • • • •
Visual slicing and dicing Flexible layout and formatting Useful for exploration Supported by Excel Services
PowerPivot pivot tables and slicers
DEMO
Data Analysis Expressions (DAX)
Excel-like syntax • Many Excel functions
Does not address cells or ranges • DAX functions refer to columns in the data
Not a replacement for MDX
Sample DAX expression = [First Name] &“ ”& [Last Name] =SUM(Sales[Amount]) =RELATED (Product[Cost])
Comment String concatenation just like Excel
SUM function takes a column name instead of a range of cells new RELATED function follows relationship between tables
Calculated Columns
Expression evaluated for each row in table Column in table is fully populated with values Can be used as a Pivot Axis or Value
Excel Functions in DAX Date and Time DATE DATEVALUE DAY EDATE EOMONTH HOUR MINUTE MONTH NOW SECOND TIME TIMEVALUE TODAY WEEKDAY WEEKNUM YEAR 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 LOG10 MINA MOD MROUND PI POWER QUOTIENT RAND RANDBETWEEN ROUND ROUNDDOWN ROUNDUP SIGN SQRT SUM SUMSQ TRUNC
Text CONCATENATE EXACT FIND FIXED LEFT LEN LOWER MID REPLACE REPT RIGHT SEARCH SUBSTITUTE TRIM UPPER VALUE
Simple calculated columns
DEMO
Relational data Filter Tables Aggregate over Tables
Follow Relationships • Retrieve data from a related table • Many-to-One • One-to-Many
Relationship Functions Related (Column) • Follow many-to-one relationship • Fetch the value from the specified column in the related table
RelatedTable (Table) • Follow relationship • Return table containing matching rows from related table • Must be used as a parameter to another function 15
Navigating relationships
DEMO
DAX Table Functions RelatedTable (Table) Filter (Table, Condition) Distinct (Column)
Values (Column) All (Table), All(Column) AllExcept (Table, Col1, Col2,...) 17
Measures
Named Expression • Not evaluated until placed into Values area of a PivotTable • Evaluated for each PivotTable cell using the filter context for that cell
DAX Aggregation Functions Aggregation functions from Excel • SUM, AVERAGE, MIN, MAX, COUNT • Take column reference, not cell
New aggregation functions • Aggregate the rows of a table • SUMX (Table, • AVERAGEX (Table, • COUNTAX (Table, • MINX (Table, • MAXX (Table,
Expression) Expression) Expression) Expression) Expression)
DAX Aggregations
DEMO
Time Intelligence Functions Navigate Date columns • So you need a Date column! • Understands days, months, quarters, and years
Three function types • Functions that return a single date • Functions that return a table of dates • Functions that evaluate expressions over time period
Functions that return a single date 1. 2. 3. 4.
FirstDate (Date_Column) LastDate (Date_Column) FirstNonBlank (Date_Column, Expression) LastNonBlank (Date_Column, Expression)
5. StartofMonth (Date_Column) 6. StartofQuarter (Date_Column) 7. StartofYear (Date_Column [,YE_Date]) 8. EndofMonth (Date_Column) 9. EndofQuarter (Date_Column) 10. EndofYear (Date_Column [,YE_Date])
Functions that return a table of dates 11. DateAdd (Date_Column, Number_of_Intervals, Interval) 12. DatesBetween (Date_Column, Start_Date, End_Date) 13. DatesInPeriod (Date_Column, Start_Date, Number_of_Intervals, Interval) 14. ParallelPeriod (Date_Column, Number_of_Intervals, Interval) 15. PreviousDay (Date_Column) 16. PreviousMonth (Date_Column) 17. PreviousQuarter (Date_Column) 18. PreviousYear (Date_Column [,YE_Date]) 19. NextDay (Date_Column) 20. NextMonth (Date_Column) 21. NextQuarter (Date_Column) 22. NextYear (Date_Column [,YE_Date])
23. DatesMTD (Date_Column) 24. DatesQTD (Date_Column) 25. DatesYTD (Date_Column [,YE_Date]) 26. SamePeriodLastYear (Date_Column)
Functions that evaluate expressions over a time period 27. TotalMTD (Expression, Date_Column [, SetFilter]) 28. TotalQTD (Expression, Date_Column [, SetFilter]) 29. TotalYTD (Expression, Date_Column [, SetFilter] [,YE_Date]) 30. OpeningBalanceMonth (Expression, Date_Column [,SetFilter]) 31. OpeningBalanceQuarter (Expression, Date_Column [,SetFilter]) 32. OpeningBalanceYear (Expression, Date_Column [,SetFilter] [,YE_Date])
33. ClosingBalanceMonth (Expression, Date_Column [,SetFilter]) 34. ClosingBalanceQuarter (Expression, Date_Column [,SetFilter]) 35. ClosingBalanceYear (Expression, Date_Column [,SetFilter] [,YE_Date])
Time Intelligence
DEMO
Resources
www.powerpivotpro.com
Twitter: donalddotfarmer
Advanced Applications Excel • Limited by data size • No data model
Excel with PowerPivot • Unlimited data • Data model enables advanced calculations
The tool of choice? • Data size is not the only factor • Consider modelling, relationships and calcs
PowerPivot Data Relational sources Multidimensional Sources File Sources
Data Feeds Clipboard Excel
Getting data into PowerPivot
DEMO
PowerPivot Relationships • Discover – Only “perfect” relationships
• Create – With hints to help
• Manage – Browse, remove
Relationships in PowerPivot
DEMO
PivotTables and Slicers Easy-of-use • • • •
Well-understood Tables and Charts Useful formatting options Help from PowerPivot
Slicers • • • •
Visual slicing and dicing Flexible layout and formatting Useful for exploration Supported by Excel Services
PowerPivot pivot tables and slicers
DEMO
Data Analysis Expressions (DAX)
Excel-like syntax • Many Excel functions
Does not address cells or ranges • DAX functions refer to columns in the data
Not a replacement for MDX
Sample DAX expression = [First Name] &“ ”& [Last Name] =SUM(Sales[Amount]) =RELATED (Product[Cost])
Comment String concatenation just like Excel
SUM function takes a column name instead of a range of cells new RELATED function follows relationship between tables
Calculated Columns
Expression evaluated for each row in table Column in table is fully populated with values Can be used as a Pivot Axis or Value
Excel Functions in DAX Date and Time DATE DATEVALUE DAY EDATE EOMONTH HOUR MINUTE MONTH NOW SECOND TIME TIMEVALUE TODAY WEEKDAY WEEKNUM YEAR 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 LOG10 MINA MOD MROUND PI POWER QUOTIENT RAND RANDBETWEEN ROUND ROUNDDOWN ROUNDUP SIGN SQRT SUM SUMSQ TRUNC
Text CONCATENATE EXACT FIND FIXED LEFT LEN LOWER MID REPLACE REPT RIGHT SEARCH SUBSTITUTE TRIM UPPER VALUE
Simple calculated columns
DEMO
Relational data Filter Tables Aggregate over Tables
Follow Relationships • Retrieve data from a related table • Many-to-One • One-to-Many
Relationship Functions Related (Column) • Follow many-to-one relationship • Fetch the value from the specified column in the related table
RelatedTable (Table) • Follow relationship • Return table containing matching rows from related table • Must be used as a parameter to another function 15
Navigating relationships
DEMO
DAX Table Functions RelatedTable (Table) Filter (Table, Condition) Distinct (Column)
Values (Column) All (Table), All(Column) AllExcept (Table, Col1, Col2,...) 17
Measures
Named Expression • Not evaluated until placed into Values area of a PivotTable • Evaluated for each PivotTable cell using the filter context for that cell
DAX Aggregation Functions Aggregation functions from Excel • SUM, AVERAGE, MIN, MAX, COUNT • Take column reference, not cell
New aggregation functions • Aggregate the rows of a table • SUMX (Table, • AVERAGEX (Table, • COUNTAX (Table, • MINX (Table, • MAXX (Table,
Expression) Expression) Expression) Expression) Expression)
DAX Aggregations
DEMO
Time Intelligence Functions Navigate Date columns • So you need a Date column! • Understands days, months, quarters, and years
Three function types • Functions that return a single date • Functions that return a table of dates • Functions that evaluate expressions over time period
Functions that return a single date 1. 2. 3. 4.
FirstDate (Date_Column) LastDate (Date_Column) FirstNonBlank (Date_Column, Expression) LastNonBlank (Date_Column, Expression)
5. StartofMonth (Date_Column) 6. StartofQuarter (Date_Column) 7. StartofYear (Date_Column [,YE_Date]) 8. EndofMonth (Date_Column) 9. EndofQuarter (Date_Column) 10. EndofYear (Date_Column [,YE_Date])
Functions that return a table of dates 11. DateAdd (Date_Column, Number_of_Intervals, Interval) 12. DatesBetween (Date_Column, Start_Date, End_Date) 13. DatesInPeriod (Date_Column, Start_Date, Number_of_Intervals, Interval) 14. ParallelPeriod (Date_Column, Number_of_Intervals, Interval) 15. PreviousDay (Date_Column) 16. PreviousMonth (Date_Column) 17. PreviousQuarter (Date_Column) 18. PreviousYear (Date_Column [,YE_Date]) 19. NextDay (Date_Column) 20. NextMonth (Date_Column) 21. NextQuarter (Date_Column) 22. NextYear (Date_Column [,YE_Date])
23. DatesMTD (Date_Column) 24. DatesQTD (Date_Column) 25. DatesYTD (Date_Column [,YE_Date]) 26. SamePeriodLastYear (Date_Column)
Functions that evaluate expressions over a time period 27. TotalMTD (Expression, Date_Column [, SetFilter]) 28. TotalQTD (Expression, Date_Column [, SetFilter]) 29. TotalYTD (Expression, Date_Column [, SetFilter] [,YE_Date]) 30. OpeningBalanceMonth (Expression, Date_Column [,SetFilter]) 31. OpeningBalanceQuarter (Expression, Date_Column [,SetFilter]) 32. OpeningBalanceYear (Expression, Date_Column [,SetFilter] [,YE_Date])
33. ClosingBalanceMonth (Expression, Date_Column [,SetFilter]) 34. ClosingBalanceQuarter (Expression, Date_Column [,SetFilter]) 35. ClosingBalanceYear (Expression, Date_Column [,SetFilter] [,YE_Date])
Time Intelligence
DEMO
Resources
www.powerpivotpro.com
