Scribd Logo
Upload

Welcome to Scribd!

  • 174 views

    Get Into Evaluation Context With DAX

    Uploaded by

    pantsaf
    This document introduces evaluation context in DAX and how functions work with it. It discusses the different types of evaluation context - row context, which is defined by calculated columns, and filter context, which is defined by reports, columns, rows and slicers. It provides examples of functions that work with each context type, such as SUMX() for row context and CALCULATE() for filter context. It also demonstrates how context behaves in multi-table scenarios and over relationships.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Get Into Evaluation Context With DAX

    Uploaded by

    pantsaf
    174 views40 pages
    This document introduces evaluation context in DAX and how functions work with it. It discusses the different types of evaluation context - row context, which is defined by calculated columns, and filter context, which is defined by reports, columns, rows and slicers. It provides examples of functions that work with each context type, such as SUMX() for row context and CALCULATE() for filter context. It also demonstrates how context behaves in multi-table scenarios and over relationships.

    Original Description:

    Powerpivot DAX

    Original Title

    Get Into Evaluation Context with DAX

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document introduces evaluation context in DAX and how functions work with it. It discusses the different types of evaluation context - row context, which is defined by calculated columns, and filter context, which is defined by reports, columns, rows and slicers. It provides examples of functions that work with each context type, such as SUMX() for row context and CALCULATE() for filter context. It also demonstrates how context behaves in multi-table scenarios and over relationships.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    174 views40 pages

    Get Into Evaluation Context With DAX

    Uploaded by

    pantsaf
    This document introduces evaluation context in DAX and how functions work with it. It discusses the different types of evaluation context - row context, which is defined by calculated columns, and filter context, which is defined by reports, columns, rows and slicers. It provides examples of functions that work with each context type, such as SUMX() for row context and CALCULATE() for filter context. It also demonstrates how context behaves in multi-table scenarios and over relationships.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 40

    Get Into Evaluation Context with DAX

    The Language of PowerPivot

    Reeves Smith
    Linchpin People
    Objective

     Introduce Evaluation Context


     Functions work with Evaluation Context
    Who am I

    Reeves Smith

    Teammate with Linchpin People and Principal Architect at Macer Consulting


    living in Denver Colorado with over 18 years of SQL Server experience.
    Focused on Business Intelligence and Data Warehousing with SQL Server
    stack for the past 12 years. Worked in industries like banking, DoD, energy,
    entertainment, gaming, healthcare, insurance, manufacturing, oil and gas,
    retail, travel, and telecommunication.

    Microsoft Most Valuable Professional in SQL Server, Microsoft Certified


    Master of SQL Server 2008 and a Microsoft Certified Solutions Expert in
    Business Intelligence and Data Platform.

    reeves.smith@macerconsulting.com or linchpinpeople.com
    @SQLReeves
    http://reevessmith.wordpress.com
    www.linkedin.com/in/reevessmithiii
    Who are you

     Who has worked with Excel?


     Who has worked with Tabular/PowerPivot?
     Who has worked with Analysis Services?
     Has anyone written DAX…
     Has anyone been to the Moon?
    Agenda

     Introduction
     Measures vs. Calculated Columns
     Evaluation Context
     Multi-Table Evaluation Context
     Questions
     References
    DAX Introduction
     What is DAX?
     Data Analysis eXpressions

     Where can I use DAX?


     PowerPivot
     Tabular

     Where do I write DAX Expressions?


     Calculated Columns
     Measures/Calculated Fields
     DAX Queries
    DAX Comparison
     Excel Expressions vs. DAX Expressions
     Excel Expressions
     Cells – A1, B3
     Ranges – A1:B3

     DAX Expressions
     Columns – [SalesAmount] or InternetSales[SalesAmount]
     Tables – InternetSales

     MDX vs. DAX Expressions


     MDX understands position, DAX does not*
     [Date].[Date].Parent
    * You can use MDX against a tabular model, but position functions will not work
    DAX – Basic Syntax

     DAX Syntax
     =[ColumnName] or =[Measure]
     Recommend Best Practice for naming Measures/Calculated Fields

     =TableName[ColumnName]
     Recommend Best Practice for naming Calculated Columns

     ='Table Name'[ColumnName]
     =[TableName].[ColumnName] Not MDX
     Column names must always be enclosed in brackets
     Table names cannot be enclosed in brackets
    Calculated Columns vs. Measures

     Calculated Columns
     Table Column Calculated
    Columns
    Calculated
    Columns

     Evaluated at process time


    Calculated Calculated
     Requires storage space Columns Columns

     Measures/Calculated Fields Calculated


    Columns
    Calculated Columns
    Measures
    Measures

     Used to aggregate data


     Evaluated at run time
     Does not require storage space

     Where can they be used within a Pivot Table


    Using Calculated Columns vs. Measures

     Calculated Columns
     Need to use in Slicer or on Rows and Columns
     Expression that is strictly bound to current row
     Categorize/Discretization

     Measures/Calculated Fields
     Calculate Percentages
     Calculate Ratios
     Save Storage Space
    Evaluation Context
    “To get the best from DAX, you need to understand the evaluation
    context.”
    Alberto Ferrari, Marco Russo or Chris Webb
    SQL Server 2012 Analysis Services the BISM Tabular Model

    What is Evaluation Context?


     Dictionary - determines the conditions and circumstances that are
    relevant to an event
     Evaluation Context is the environment in which a DAX formula is
    calculated
     Every DAX expression is evaluated within a context
    What Does the Below Value Return?

    =SUM( FactInternetSales[SalesAmount] )

    It depends…
    Evaluation Context Types

     Types of Evaluation Contexts in DAX?


     Row Context – The current row within a table
     Filter Context – The set of active rows in a
    calculation

     Query Context* – The subset of data implicitly


    retrieved within formula

    * Query context only referenced by Microsoft and I have not seen it used in any other
    documents.
    Row Context
     Defined by Calculated Column
    Row Context Functions

     AVERAGEX()
     COUNTX()
     MINX()
     MAXX()
     SUMX()
     EARLIER()
     FILTER()
    SUMX() Function

     Returns the sum of a expression evaluated for


    each row in a table
    SUMX( table, expression )

     Measures/Calculated Fields do not contain a


    row context
    Filter Context
     Defined by Report, Column, Row, Slicers
    Filter Context Functions

     CALCULATE()
     CALCULATETABLE()
     FILTER()
     ALL()
     ALLEXCEPT()
     ALLSELECTED()
     VALUES()
    FILTER() Function

     Returns a table that represents a subset of another table


    or expression
     Function is able to replace filter context

    FILTER( table, filter )


    CALCULATE() Function

     Evaluates an expression in a context that is modified by


    the specified filters
     Is the only function that is able to modify filter context

    CALCULATE( expression, filter1, filter2…)

     Two types of conditions


     Boolean Conditions, that work on a single column
     List of Values, presented as table
    CALCULATE() vs. FILTER()

     CALCULATE looks at a column scope


    CALCULATE() vs. FILTER()

     FILTER looks at the table scope


    ALL() Function

     Clears any filters from a table or individual columns


    ALL( table | column1 [,column2 …] )
    Testing Evaluation Context

     Excel 2010/2013
     PowerPivot Window – Table Filters
    Testing Evaluation Context

     SQL Server Data Tools (SSDT)


     Table Filters
    Testing Evaluation Context

     SQL Server Management Studio (SSMS)


     DAX Queries
    Demo – Row and Filter Context
    EARLIER() Function
    EARLIER() Function
    Row Context – Multiple Tables

     Row Context Does Not Propagate over Table


    Relationships
     Working on the Many Side of a One–to–Many

    RELATED(Products[ListPrice])

    1
    Products
    ∞Orders
    Row Context – Multiple Tables

     Row Context Does Not Propagate over Table


    Relationships
     Working on the Many Side of a One–to–Many
     Working on the One Side of a One–to–Many
    RELATEDTABLE(Orders)

    1
    Products
    ∞Orders
    Filter Context – Multiple Tables

     Filter Context Does Propagate over Table


    Relationships
     Apply Filter on the One Side of a One–to–Many

    1
    Products
    ∞
    Orders
    Filter Context – Multiple Tables

     Filter Context Does Propagate over Table


    Relationships
     Apply Filter on the One Side of a One–to–Many
     Apply Filter on the Many Side of a One–to–Many

    1
    Products
    ∞
    Orders
    Model Comparisons

    1
    Products
    ∞
    Orders
    Demo – Multi-Table Row and Filter Context
    Questions

     How do I get started?


     Excel 2013 and PowerPivot
    PowerPivot is only available with volume licenses*
    http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2013/02
    /27/unable-to-use-excel-2010-powerpivot-workbooks-in-excel-2013.aspx

    http://www.powerpivotpro.com/2013/02/guest-post-from-ken-puls-how-to-buy-
    powerpivot-2013-including-the-30-volume-licensing-workaround/

    * As of August 2013 can purchase Excel 2013 stand-


    alone version that works PowerPivot
    http://www.powerpivotblog.nl/power-pivot-and-power-view-now-available-in-excel-
    stand-alone/
    References – Books
    DAX Excel Server
    References - Blogs
     SQLBI - Marco Russo & Alberto Ferrari
    http://www.sqlbi.com/
     Marco Russo
    http://sqlblog.com/blogs/marco_russo/default.aspx
     Alberto Ferrari
    http://sqlblog.com/blogs/alberto_ferrari/default.aspx
     Chris Webb
    http://cwebbbi.wordpress.com/
     PowerPivotPro - Rob Collie
    http://www.powerpivotpro.com/
     Javier Guillén
    http://javierguillen.wordpress.com/
     Gerhard Brueckl
    http://gbrueckl.wordpress.com/
     Stairway to DAX
    http://www.sqlservercentral.com/stairway/75426/
    References - Tools
     BIDS Helper
    http://bidshelper.codeplex.com/
     DAX Studio
    http://daxstudio.codeplex.com/
    Thanks

    reeves.smith@macerconsulting.com or linchpinpeople.com
    @sqlreeves
    http://reevessmith.wordpress.com
    www.linkedin.com/in/reevessmithiii

    You might also like