Example Materials Power BI Desktop File 416 https

Example Materials • Power BI Desktop File (4/16): https: //drive. google. com/file/d/0 B 4 d. Dgy. Qg 6 WPb. WY 5 UTRha. HYwd. DA/view? usp=sharing

Intro to DAX Patterns

• Eric Bragas – MCP, PSM I • Design. Mind - Business Intelligence Consultant

Agenda • DAX • Data Analysis Expressions • Notation • Functions • Evaluation Contexts • Measure Patterns • Calculated Column Patterns

Data Analysis Expressions (DAX) • What is DAX? • DAX is a language that allows us to write dynamic expressions for relataional constructs, using familiar functions • Powerful dynamic data analysis tool for relational data • Expressions can traverse relationships! • Available in Power. Pivot, Power. BI, and SSAS Tabular • Classic “Import Mode” • What isn’t DAX? • NOT a programming language

DAX Notation Table ‘Table’ Column ‘Table’[Column] Function fn ( <arguments> ) Measure Name : = SUM ( ‘Table’[Column] ) Calculated Column Name = SUM ( ‘Table’[Column] )

Measures • A measure is a formula/expression comprising functions applied to columns and tables • Reusable aggregation evaluated differently, depending on how you use it • Measures can be nested

Functions • Logical • IF( logical test, <value if true>, <value if false> ) • SWITCH( <expression>, <value>, <result>, … ) – evaluates an expression against a list of values, and returns the result corresponding to the first matching value • TRUE() – returns logical true • Aggregate • SUM( <column> ) – adds all the numbers in a column • DIVIDE( <numerator>, <denominator>, [, <alternateresult>] ) – basic division; optional value returned • Statistical • MAX( <column> ) – returns the largest numeric value in a <column> • MIN( <column> ) – returns the smallest value in a <column> • Text • BLANK() – returns a blank • Filter • FILTER(<table>, <filter>) – returns a table representing a subset of another table or expression • ALL( <table> | <column>) – returns all the rows in a table, ignoring any filter context • VALUES(<table or column>) – returns one column of the distinct values from the specified column or table • CALCULATE( <expression>, <filter 1>, <filter 2>, … ) – evaluates an expression in a context that is modified by the specified filters

Functions (cont’d) • Date and Time • TODAY() – returns the current date • NOW() – returns the current date and time in datetime format • Time Intelligence* • DATESBETWEEN(<dates>, <start date>, <end date>) – returns a table of <dates> starting with the <start date> and continues until the <end date>. • NEXTDAY(<dates>) – returns a table that contains a column with the next dates following each of the <dates> passed • FIRSTDATE(<dates>) – returns the first date in the context of the specified column of dates • LASTDATE(<dates>) – returns the last date in the context of the specified column of dates • SAMEPERIODLASTYEAR(<dates>) – returns a table with a column of dates shifted one year back for each of the <dates> specified • LASTNONBLANK( <column>, <expression> ) – returns last value in the <column> where the <expression> returns blank • FIRSTNONBLANK( <column>, <expression> ) – returns the first value in the <column> where the <expression> returns blank *Requires Date Table

Evaluation Contexts • Evaluation Contexts: • Filter Context • Four types of filter context: 1. 2. 3. 4. Row Selection Column Selection Slicer Selection Filter Selection • Defines the subset of data a measure is calculated using aka “Which rows are selected based on which attribute values? ” • Applied before anything else • Row Context • All the columns in the Current Row “DAX is simple, it’s not easy, but it’s simple” - Alberto Ferrari

Basic Evaluation Context - Demo Total. Sales = SUM ( Sales[Sales. Amount] )

Anatomy of a Filter Context - Pivot. Table Filt ns er m u l o C Rows Slicer

Anatomy of a Filter Context - Chart Slicer Filter n m Colu

Measure Patterns Basic Cumulative YTD Year Over Year Semi-additive Disconnected Slicers

Basic Measures • SUM( ‘Sales’[Sales. Amount] ) • AVERAGE( ‘Inventory’[Inventory. On. Hand] ) • MIN( ‘Weather’[Temp] ) • MAX ( ‘Weather’[Temp] ) • Etc.

Basic Measure Demo – Power. Pivot & Power. BI Asia Sales : = CALCULATE ( [Total Sales], Geography[Continent. Name] = "Asia" ) Asia Sales : = CALCULATE ( [Total Sales], FILTER ( 'Geography', Geography[Continent. Name] = "Asia" ) ) Boolean Table

Cumulative Total Measures • Aggregates values of a column for the currently selected date and all previous dates within the specified range • Can be used to derive balances from transactions eg. • Inventory Stock • Balances • Cumulative Balances • Does not require use of Time Intelligence functions

Cumulative Measure Demo - Power. BI Cumulative Energy Generated (Checked) = IF ( MIN ( 'Date'[Date] ) <= MAX ( ‘Output’[Date], ALL ( ‘Output’ ) ) , CALCULATE ( SUM ( 'Output'[Energy Generated] ), FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )

Year-to-Date Total • TOTALYTD function applies the expression for all data from the start of the year to the currently selected date in the filter context Year To Date = TOTALYTD( <expression>, <dates> [, <filter>] [, <year end date>] )

Year-to-Date Total Demo - Power. BI Total Energy Generated YTD = TOTALYTD ( SUM ( 'Output'[Energy Generated] ), 'Date'[Date] )

Year Over Year • Use time intelligence to calculate an aggregate for the same period last year • “Last Year” measure is used to compare to “Current Year” measure, and/or to derive a measure of the change year-over-year

Year-Over-Year Demo – Power. BI Total Energy Generated Last Year = CALCULATE ( [Total Energy Generated], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Semi-Additive Measures • Snapshot Fact Table with balance values, such as Inventory or Account Balances • These scenarios disallow us from summing across time • The solution is to sum across all attributes except for time by filtering for only a single point in time (eg. last date in the period) • Several functions allow you to adjust filter context to a single point in time, within the original context period • FIRSTDATE / LASTDATE • FIRSTNONBLANK / LASTNONBLANK • OPENING… / CLOSING…

Semi-Additive Measure Demo Power. Pivot Total On Hand Quantity LASTNONBLANK = CALCULATE ( SUM ( Inventory[On. Hand. Quantity] ), LASTNONBLANK ( 'Date'[Date], CALCULATE ( SUM ( Inventory[On. Hand. Quantity] ) )

Disconnected Slicers Allows you to use a slicer to modify measures • Measure Switching • Used to switch between a set of measure values in a container measure

Disconnected Slicers Demo - Power. BI Setup Steps: 1. Create/identify your target measures (eg. [Energy Exported] & [Energy Generated]) 2. Create disconnected table to use in slicer selection 3. Create background “value selection measure” using MAX() • Hide this! 4. Create SWITCH measure to use in visualizations

Calculated Column Patterns Value Binning

Value Binning • Used to group similar values, or to bin values for analysis aka Histograms • Can bin values based on equality, or inequality comparisons with the SWITCH() function • Use Cases: • Age groups • Product Groups • Any kind of frequency distributions

Value Binning Demo - Power. BI Inventory Age (bin) = SWITCH ( TRUE (), 'Inventory'[Days. In. Stock] "101+" ) < < 20, "0 -20", 50, "21 -50", 80, "51 -80", 100, "81 -100",

Summary • DAX is dynamic because you can write measures that correctly evaluate under their current Evaluation Context • Filter Context • Row Context • Functions are the building blocks of our measures and perform a myriad of tasks eg. altering Context, aggregating, logical operations, time intelligence, etc • Time Intelligence functions require a Date table to operate • Understanding Tabular Data Modeling will go a long way towards helping your understanding of DAX

Thanks!