Required Slide

Presentation Transcript

Required Slide

Required Slide SESSION CODE: BIU05-INT DAX Patterns in PowerPivot Marco Russo BI Architect & Consultant SQLBI

Who is the speaker marco.russo@sqlbi.com Independent specialist 15+ years on SQL Server 10+ years on BI & OLAP Book writer Expert Cube Development with Microsoft SQL Server Analysis Services PowerPivot for Excel 2010: Give your information a significance Founder of SQLBI.COM

Agenda DAX Foundations DAX Syntax Calculated Columns, Measures, Evaluation Context DAX at work Weight Percent, ABC/Pareto, Time Period Comparisons Customers/Products ( particular tally )

Data Analysis Expressions (DAX) DAX utilizes a grammar like Excel TableX [ ColY ] or [ ColY ] (completely qualified versus not qualified) Cells can't be referenced (like B23, B12:C15, … ) DAX capacities work with table segments

DAX is More Powerful than Excel Formulas DAX incorporates ideas from social databases Multiple tables Filters, cycle and accumulation of tables/sections Relationships (references to information in related table) Dynamic collection of measures in rotate tables Analyze and change Filter Context "Time Intelligence" capacities DAX is not a trade for (MDX is utilized as a part of Analysis Services) DAX characterizes estimations in PowerPivot – for End Users MDX characterizes multidimensional inquiries – for IT Pros

Using DAX Calculated Columns Regular segments populated with information read by outer sources Data is duplicated into PowerPivot Expressions in ascertained segments assessed for each line Result put away amid information procurement And when computation is characterized first time Each assessment utilizes the "Push Context"

Using DAX Measures (or Calculated Field) Named expressions for turn tables Belong to the PowerPivot show, not to the turn table Each cell assesses equation in a particular "Channel Context" Some counts requires measures (i.e. % of aggregate)

Sample PowerPivot Model DEMO

Calculated Columns

Calculated Columns DEMO

Measures - Implicit versus Unequivocal

Measures DEMO

Evaluation Context

Functions that Follow Relationships

DAX Aggregation Functions Aggregation capacities require a segment reference Implicit setting in DAX/PowerPivot In Excel there is dependably an express setting utilizing cell ranges Aggregation of an expression assessed for each column of a table SUMX (Table, Expression) AVERAGEX (Table, Expression) COUNTAX (Table, Expression) MINX ( Table, Expression) MAXX (Table, Expression)

More than 80 Excel works in DAX

Table Functions in DAX Differences from Excel

Table Functions in DAX Functions List

Making Calculation by Modifying Filter Context

Measures Examples

Measures with Calculate edge % on Sales Table DEMO

DAX at work Weight Percent ABC/Pareto Time Period Comparison Customers/Products (particular check) Many-to-numerous connections

Weight Percent Weight Percent estimation requires a DAX express measure i = esteem/rif DAX equation contains an express figuring of the denominator

Weight Percent DEMO

ABC/Pareto

ABC/Pareto SalesAmountProduct Calculate add up to deals sum for every item Version on standardized table Some progressions required for denormalized tables Better execution with standardized table SalesAmountProduct = SUMX ( RELATEDTABLE( Orders ), Orders[Amount] )

ABC/Pareto CumulatedProduct Calculate the cumulated deals sum for every item Sort items for deals sum (descendent request) Sum up deals sum for all items having deals sum >= of «current» item ( push setting) C umulatedProduct = SUMX ( FILTER ( Products, Products[ SalesAmountProduct ] >= EARLIER ( Products[ SalesAmountProduct ] ) , Products[ SalesAmountProduct ] )

ABC/Pareto SortedWeightProduct Ratio amongst CumulatedProduct and aggregate deals for all items Needed to assess limit of ABC classes (70%, 20%, 10%) SortedWeightProduct = Products[CumulatedProduct ]/SUM( Products[SalesAmountProduct] )

ABC/Pareto ABC Product Defines an A/B/C property in light of SortedWeightProduct ABC Product = IF ( Products[ SortedWeightProduct ] < 0.7, " A", IF ( Products[ SortedWeightProduct ] < 0.9, " B", " C" )

ABC/Pareto DEMO

Time Period Comparison Year-To-Date

Time Period Comparison Previous Year

Time Period Comparison Last 12 months

Time Period Comparison DEMO

Number of Unique Customers/Products Distinct Count Measure There is no DISTINCT COUNT conglomeration It can be fabricated utilizing COUNTROWS and DISTINCT Customers = COUNTROWS ( DISTINCT ( Orders[CustomerID] ) More intricate recipe required at whatever point a related table is included Cities = CALCULATE ( COUNTROWS ( FILTER ( DISTINCT ( Customers [City] ) , COUNTROWS ( RELATEDTABLE ( Orders ) > 0 )

Unique Customers/Products How to actualize a Distinct Count computation DEMO

Many-to-numerous relationship Formed by two one-to-numerous connections Natively not upheld by PowerPivot Can be ascertained by utilizing DAX recipes in view of COUNTROWS over middle of the road table (otherwise known as extension table of factless certainty table) http://sqlblog.com/online journals/marco_russo/document/2009/12/07/many-to-numerous connections in-powerpivot.aspx

Summary

Links Blog http://sqlblog.com/web journals/marco_russo ABC investigation in PowerPivot http ://sqlblog.com/sites/marco_russo/file/2010/01/19/abc-examination in-powerpivot.aspx Time Intelligence Functions http://blogs.msdn.com/powerpivot/chronicle/2010/04/12/time-knowledge capacities in-dax.aspx Book about PowerPivot from Microsoft Press Expected for September 2010 - Authors: Marco Russo and Alberto Ferrari Other great assets: www.powerpivot.com www.powerpivot-info.com http://powerpivotfaq.com/www.powerpivotpro.com

Required Slide Resources Learning Sessions On-Demand & Community Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning Resources for IT Professionals Resources for Developers http://microsoft.com/technet http://microsoft.com/msdn

Required Slide Speakers, please list the Breakout Sessions, Interactive Sessions, Labs and Demo Stations that are identified with your session. Related Content BIU302 – Enriching Microsoft PowerPivot for Microsoft Excel Applications Using DAX Thursday, June 10  |  9:45 AM - 11:00 AM  |  Rm 244 BIU02-INT – Budgeting Solution Built with PowerPivot Thursday, June 10  |  1:30 PM - 2:45 PM  |  Rm 240 BIU02-HOL – Defining DAX Calculations with MS PowerPivot for MS Excel 2010

Required Slide Complete an assessment on CommNet and enter to win!

© 2010 Microsoft Corporation. All rights saved. Microsoft, Windows, Windows Vista and other item names are or might be enrolled trademarks as well as trademarks in the U.S. as well as different nations. The data in this is for instructive purposes just and speaks to the present perspective of Microsoft Corporation as of the date of this introduction. Since Microsoft must react to changing economic situations, it ought not be deciphered to be a promise with respect to Microsoft, and Microsoft can't ensure the exactness of any data gave after the date of this introduction. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.