DAX Gently A fast paced but sequential approach to learn - PowerPoint Presentation

Slide1

DAX Gently

A fast paced but sequential approach to learnData Analysis eXpressions

Alnis Bajars.

alnis@bajars.com

@alnisb

Slide2

Agenda

DAX Gently

Recap of Advantage

s of Power Pivot

Fundamentals of Power Pivot EnvironmentCalculated ColumnsFirst Pivot Table ex Power PivotCalculated Fields aka MeasuresFunctions and their use

NOTE: Download Adventure Works DW 2014 (or recent version) to do the demos yourself.

Load

FactInternetSales

, selected Related Tables, select Product

SubCat

and Cat.

Slide3

Assumptions

So we don’t get bogged down.

Excel 2013 only (not 2010), and you can enable Power Pivot. (Note: Power BI Desktop emerging)

You know where Power Pivot fits into Power BI

You know the difference between Personal BI, Team BI and Corporate BIYou know the basic Power Pivot environment.You know how to acquire data in Power PivotYou understand the concept of the Data Model

Slide4

Hot off the Press!

Power BI Desktop coming July 24.

Power BI Designer Preview going live

Separates Power BI from Excel (corporate friendly)

No impediment to using 64 bit versionhttp://blogs.msdn.com/b/powerbi/archive/2015/07/10/announcing-power-bi-general-availability-coming-july-24th.aspx

Slide5

References

DAX Patterns 2015

By Marco Russo and Alberto Ferrari (The Italians)

Fast paced yet rigorous, does not over explain concepts

Other Books by the ItaliansSQL Server 2012 Analysis Services: The BISIM Tabular ModelExcel 2013: Building Data Models with PowerPivotVery rigorous and detailed, can be hard to digestBooks by Rob Collie, Bill Jelen, Kapser de

Jonge

Widely available on Kindle. Some easy to digest explanations, lacks rigour in places

Power Pivot Succinctly by James Beresford

Best possible price

Great overview of Power Pivot ecosystem. Explains DAX well.

Engine deep dive.

Slide6

Recap

Advantages of Power Pivot

Slide7

Advantages of Power Pivot

Breaks Excel 1 million row barrier

Fast and compressed

xVelocity

(ex Vertipaq), cf SSAS Tabular)Eclectic range of data sourcesData modelling, no cell referencesNo Vlookup! The streets are safe again.Can do a lot of things Excel formulas can’t easily do.Prototype SSAS Tabular

Slide8

But be warned

Looks like simple Excel functions .. But be warned.

Mismatch of expectations by Microsoft

Subtleties of row context and filter context are not easy to master

Not necessarily a power user toolAnd this is the focus of this presentation. Fast track over the bleeding obvious and focus on the more challenging concepts.

Slide9

MDX vs DAX

Power Pivot Succinctly by James Beresford.

Slide10

Fundamentals of

Power Pivot Environment

Slide11

Getting Started

Enable Power Pivot in Excel 2013.

Power Pivot: Switch to Advanced Mode

Slide12

Demo: Relationships View

Free us from the tyranny of cells and

Vlookups

Auto create known relationships

Manually create relationshipsCardinality is important: 1-many vs many-1No direct support for many-many, simple workarounds laterMultiple relationships – but only one active (no direct role playing)

Slide13

Demo: Hierarchies

As per OLAP

Create in Diagram View

Either drag drop members, or right mouse click

Slide14

Data Manipulation Notes

Limited # of data types. Text, Decimal, Whole, Currency, TRUE/FALSE

Compression good, except for Decimal/Currency

Note BLANK() function.

Behaviour not always obvious for arithmetic and Boolean.DIVIDE function handles div by zero, optional parameter to override BLANK(). Cleaner code and performs better than IF.

Slide15

Functions: The Bleeding Obvious

Slide16

Demo: Cleanse Data

Delete columns not needed cf. Filter at Preview

cf

filter at data sourceHelper columns not needed by user: Hide From Client ToolsFormat columns

Slide17

Demo: Mark as Date Table

Essential prerequisite for advanced time intelligence functions

Design -> Mark as Date Table

Nominate a Unique Column

Also sort Month Name by Month Number of Year

Slide18

Calculated Columns vs Calculated Fields

Calculated Columns

Custom columns

Applies row by row

, individual cells cannot be editedBy default, ROW CONTEXTWarningFilters can be enforced, overridden or transitionedContext must be understood to understand many of the powerful functions.

Calculated Fields

Called Measures in Excel 2010 (and Power BI Designer!)

Must be aggregates of some sort

By default,

FILTER CONTEXT

This is the KEY takeaway slide (hence Gold colour heading)..

Slide19

Context Overview

Row Context

Custom columns

Applies row by row

Individual By default, ROW CONTEXTFilter ContextCalled Measures in Excel 2010Must be aggregates of some sortBy default, FILTER CONTEXT

Slide20

Calculated

Columns

Slide21

Calculated Columns Basics

An extra column!

Format [Column Name] = <Formula>

Table Qualification

Applies row by row See later contrasts with Filter ContextRow ContextCan use [Field Name]Can use Table[Field Name} for disambiguation cf SQL

Slide22

Demo: Calculated Columns

Note the ways to name the column

Note references to other columns cf. “official tables”

Note behavior of aggregations

Slide23

Demo: Reference Other Tables

Premature look at first functions

RELATED() when direction from many to 1

RELATEDTABLE() when direction 1 to many

Will traverse relationships for as long as relationships of the same typeSneak preview. CALCULATE auto works relationships 1-many

Slide24

EARLIER

Each pass of calculation engine creates a row

context level

Acts like “CURRENTROW”, remembers previous row context

EARLIER(<column>, <skip_levels> )skip_levels defaults to 1Eg.ListPriceRank= COUNTROWS ( FILTER ( DimProduct, DimProduct[ListPrice] > EARLIER ( DimProduct[ListPrice] ) )) + 1

Slide25

First Pivot Table

From Power Pivot

Slide26

Demo: Create Pivot Table

Note all tables visible, except “Hide From Client Tools”

Otherwise a lot like Pivot Tables … so far

Slide27

Calculated Fields

And Filter Context

Slide28

Demo: First Calculated Fields

Enter below the line

Note Pascal like assignment [Calculated Column] := <Formula>

Enter from Calculated Fields button

Note the default behaviour of filter context!

Slide29

Aggregation Function Types

Standard Functions

AVERAGE, MAX, MIN, SUM

Can only pass one column as an argument

Append with XAVERAGEA, MAXA, MINAIf text, returns 0 instead of errorAppend with ASolves the one column problem

<Function>X(Table, Expression)

SUMX( Sales, Sales[

OrderQuantity

] * Sales[

UnitPrice

] )

AVERAGEX, MAXX, MINX, SUMX

Slide30

Count Functions

COUNT, COUNTA

COUNTX, COUNTAX

COUNTBLANK

COUNTROWSCOUNTDISTINCT

Slide31

Essential Functions

And their use

Slide32

FILTER

Supply a table and filters

Returns table subset (cf. WHERE clause), still a table

FILTER(<table>, <filter1>, <filter2> …>

Eg.SumHiValueProd :=SUMX( FILTER(FactInternetSales, FactInternetSales[UnitPrice]>100), FactInternetSales [OrderQuantity] * FactInternetSales [UnitPrice] )

Slide33

CALCULATE

Probably the most important DAX function

Roll your own

filter context, including none at all

Still obeys active filtersCALCULATE(<expression>, <filter1>, <filter2> …>Eg.[Sales 2011] := CALCULATE ( SUM(FactInternetSales[SalesAmount]), DimDate[CalendarYear] = 2011)

Slide34

ALL

Effectively removes filter each time a calculated field is executed

Eg

.

[Sales 2011 ALL] := CALCULATE ( SUM(FactInternetSales[SalesAmount]), DimDate[CalendarYear] = 2011, ALL(DimDate))

Slide35

HASONEVALUE

TRUE if current context filtered to just one value, otherwise FALSE

Eg

.

[ResellerSales compared to 2011] :=IF(HASONEVALUE(DateTime[CalendarYear]),SUM(ResellerSales[SalesAmount])/CALCULATE(SUM(ResellerSales[SalesAmount]),DateTime[CalendarYear]=2011),BLANK())

Slide36

USERELATIONSHIP

Make one of multiple relationships between tables active

Workaround for lack of support for

role playing dimensions

Eg.[Sales by Ship Date]=CALCULATE(SUM(FactInternetSales[SalesAmount]), USERELATIONSHIP(FactInternetSales[ShipDateKey], DimDate[DateKey]))

Slide37

First Look at Time Intelligence

Functions very difficult to do in Excel

Huge choice

https://

msdn.microsoft.com/en-us/library/ee634763.aspxEg 1[SalesYTD] := TOTALYTD( Sum(FactInternetSales[SalesAmount]), DimDate[FullDateAlternateKey] )Eg 2[PYSales] :=CALCULATE(Sum(FactInternetSales[SalesAmount]), SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]) )

Slide38

Many to Many Relationship

A number of workarounds,

eg

.

SUMMARIZE( Bridge_AccountNumber, DimAccount[ID_Account])

Slide39

Summary

Basic DAX simple and intuitive ….But there are a lot of subtle tricks.

Lots of in depth material from

The Italians

ie. Marco Russo and Alberto FerrariFuture Gently Talks

If you think this format works well….

M Gently (Power Query)

R Gently (Machine Learning/ Predictive Analysis)

Alnis Bajars. Email:

alnis@bajars.com

Twitter: @alnisb