100%(1)1 out of 1 people found this document helpful
This preview shows page 88 - 92 out of 109 pages.
Table to be filteredExamples:•Territory_Lookup•Customer_LookupA 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 expressionPRO 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 thingHEY THIS IS IMPORTANT!FILTER is used to add new filter context, and can handle more complex filter expressionsthan CALCULATE (by referencing measures, for example)Since FILTER returns an entire table, it’s almost always used as an inputto other functions, like CALCULATE or SUMXFILTER*Copyright 2018, Excel Maven & Maven Analytics, LLC
Iterator(or “X”) functionsallow 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 theexpression will be evaluatedExamples:•Sales•FILTER(Sales, RELATED(Products[Category])=“Clothing”)Expression to be evaluated foreach row of the given tableExamples:•[Total Orders]•Sales[RetailPrice] * Sales[Quantity]Aggregation to apply to calculated rows*Examples:•SUMX•COUNTX•AVERAGEX•RANKX•MAXX/MINXPRO TIP:Imagine the function adding a temporary new columnto 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 syntaxITERATOR (“X”) FUNCTIONS*Copyright 2018, Excel Maven & Maven Analytics, LLC=SUMX(Table, Expression)
=CALCULATE(Measure,DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -10, DAY))Time Intelligencefunctions allow you to easily calculate common time comparisons:=CALCULATE(Measure, DATESYTD(Calendar[Date]))=CALCULATE(Measure, DATEADD(Calendar[Date], -1, MONTH))PerformanceTo-DateUseDATESQTDfor Quarters or DATESMTDfor Months PreviousPeriodSelect an interval (DAY, MONTH,QUARTER,orYEAR) and the # of intervals to compare (i.e. previous month, rolling 10-day)RunningTotalPRO TIP:To calculate a moving average, use the running total calculation above and divide by the number of intervalsTIME INTELLIGENCE FORMULAS*Copyright 2018, Excel Maven & Maven Analytics, LLC
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 formulasReference 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