Table to be filtered Examples TerritoryLookup CustomerLookup A Boolean

Table to be filtered examples territorylookup

This preview shows page 88 - 92 out of 109 pages.

Table to be filtered Examples: Territory_Lookup Customer_Lookup A Boolean (True/False) filter expression to be evaluated for each row of the table Examples: Territory_Lookup[Country] = “ USA Calendar[Year] = 1998 Products[Price] > [Overall Avg Price] = FILTER ( Table , FilterExpression) FILTER () Returns a table that represents a subset of another table or expression PRO TIP: Since FILTER iterates through each row in a table, it can be slow and processor- intensive; don’t use FILTER if a CALCULATE function will accomplish the same thing HEY THIS IS IMPORTANT! FILTER is used to add new filter context, and can handle more complex filter expressions than CALCULATE ( by referencing measures, for example ) Since FILTER returns an entire table, it’s almost always used as an input to other functions, like CALCULATE or SUMX FILTER *Copyright 2018, Excel Maven & Maven Analytics, LLC
Image of page 88
Iterator (or “ X ”) functions allow you to loop through the same calculation or expression on each row of a table , and then apply some sort of aggregation to the results ( SUM , MAX , etc) Table in which the expression will be evaluated Examples: Sales FILTER(Sales, RELATED(Products[Category])=“Clothing”) Expression to be evaluated for each row of the given table Examples: [Total Orders] Sales[RetailPrice] * Sales[Quantity] Aggregation to apply to calculated rows* Examples: SUMX COUNTX AVERAGEX RANKX MAXX/MINX PRO TIP: Imagine the function adding a temporary new column to the table, calculating the value in each row (based on the expression) and then applying the aggregation to that new column (like SUMPRODUCT) *In this example we’re looking at SUMX , but other “X” functions follow a similar syntax ITERATOR (“ X ”) FUNCTIONS *Copyright 2018, Excel Maven & Maven Analytics, LLC = SUMX ( Table , Expression )
Image of page 89
= CALCULATE (Measure, DATESINPERIOD (Calendar[Date], MAX (Calendar[Date]), -10, DAY )) Time Intelligence functions allow you to easily calculate common time comparisons: = CALCULATE (Measure, DATESYTD (Calendar[Date])) = CALCULATE (Measure, DATEADD (Calendar[Date], -1, MONTH )) Performance To-Date Use DATESQTD for Quarters or DATESMTD for Months Previous Period Select an interval ( DAY , MONTH , QUARTER , or YEAR ) and the # of intervals to compare (i.e. previous month, rolling 10-day) Running Total PRO TIP: To calculate a moving average , use the running total calculation above and divide by the number of intervals TIME INTELLIGENCE FORMULAS *Copyright 2018, Excel Maven & Maven Analytics, LLC
Image of page 90
Write measures for even the simplest calculations (i.e. Sum of Sales) Once you create a measure it can be used anywhere in the report and as an input to other, more complex calculations (no implicit measures!) Break measures down into simple, component parts DAX is a difficult language to master; focus on practicing and understanding simple components at first, then assemble them into more advanced formulas Reference columns with the table name, and measures alone Using “fully qualified” column references ( preceeded by the table name) helps make formulas more readable and intuitive, and differentiates them from measure references
Image of page 91
Image of page 92

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture