USEFUL DATA ANALYSIS EXPRESSIONS (DAX) FUNCTIONS FOR BEGINNERS
DAX (Data Analysis Expression) is a language that PowerPivot uses to create calculations between the columns in the excel data model and also can be used in the Power BI. The difference between the Excel and DAX- Excel works on cell references and ranges of cells whereas DAX works on tables and subsets of tables of data. DAX has many powerful functions which Excel does not have.
DAX Operators
Sources: docs.microsoft.com/en-us/dax/dax-operator; referencetutorialspoint.com/excel_dax/excel_dax_arithmetic_operators.htm
Using && and || functions for logical test:
A sample data table with 269,720 rows.
SUM and SUMX Functions
DIVIDE : Performs division and returns alternate result or BLANK() on division by 0.
DIVIDE(<numerator>, <denominator> [,<alternateresult>])
Logical Function: IF Statement
DATEDIFF function: Returns the count of interval boundaries crossed between two dates.
DATEDIFF(<start_date>, <end_date>, <interval>)
CALCULATE Function:
CALCULATE Function with FILTER :
=CALCULATE(<expression>, <filter1>, <filter2>,…
SWITCH Function: Evaluates an expression against a list of values and returns one of multiple possible result expressions.
SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
Time Intelligence Functions
Month To date, Quarter to data & Year to date functions
QTD Transactions: =CALCULATE([Total Transactions],DATESQTD('Calendar-Lookup'[Date]))
YTD Transactions: =CALCULATE([Total Transactions],DATESYTD('Calendar-Lookup'[Date]))
Rolling Period Total
10 days Moving Average:
RELATED Function :returns a related value from another table.
RELATED(<column>)
RANKX :Returns the ranking of a number in a list of numbers for each row in the table argument.
RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])
SAMEPERIODLASTYEAR: Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.
