Use case : Aggregating
a column of values into a single number.
e.g. Average
customer age, Maximum
product profit, Sum
of revenue, Count
of orders, etc.
FuntionName ( Argument 1, Argument 2, [Argument 3] )
- Argument specified in square brackets
[]
areoptional
arguments. - Arguments specified without square brackets
[]
aremandatory
arguments. - Few functions does not require any argument. e.g. NOW ( ), TODAY ( )
Aggregation Functions | |
---|---|
SUM | Sum of all the values in a column. |
AVERAGE | Mean of all the values in a column. |
MIN | Minimum of all the values in a column. |
MAX | Maxmimum of all the values in a column. |
COUNT | Count of all the values in a column. |
COUNTA | Count of all the non empty values in a column. |
COUNTROWS | Count of the number of rows in a column. |
DISTINCTCOUNT | Count of all the unique rows in a column. |
// Better way to count the Distinct Rows.
Total Employee =
COUNTROWS (
VALUES (
'Employee Lookup'
)
)
Iterator Functions | |
---|---|
SUMX | Sum of all the values in a column. |
AVERAGEX | Mean of all the values in a column. |
MINX | Minimum of all the values in a column. |
MAXX | Maxmimum of all the values in a column. |
COUNTX | Count of all the values in a column. |
// How the Code is written
Measure Name =
SUM ( 'Table Name'[Column Name] )
// How it's interpreted by DAX
Measure Name =
SUMX (
'Table Name',
'Table Name'[Column Name]
)
Converting
fields into desired formats i.e. Text
to Dates
, Integers
to Currency
, etc.
Rounding Functions | |
---|---|
INT(Number) | Round a number to an Integer. |
ROUND(Number, Digit) | Round a number to a specific digit. |
ROUNDUP(Number, Digit) | Round a number up. |
ROUNDDOWN(Number, Digit) | Round a number down. |
MROUND(Number, Multiple) | Round a number to desired multiple. |
TRUNC(Number) | Remove decimals. |
FIXED(Number) | Round a number down and return as text. |
CEILING(Number) | Round up a number to nearest integer. |
FLOOR(Number) | Round down a number to nearest integer. |
// Decimal Value
INT(3.14567) = 3
ROUND(3.14467, 2) = 3.14
ROUNDUP(3.14467, 2) = 3.15
ROUNDDOWN(3.14467, 2) = 3.14
FIXED(3.14467, 2) = '3.14'
// Time Value
MROUND(9:34:15 AM, "0.15") = 9:30:00 AM -- Minute Round.
FLOOR(9:34:15 AM, "0.15") = 9:30:00 AM -- Rounds the minute component down to nearest multiple.
CEILING(9:34:15 AM, "0.15") = 9:45:00 AM -- Rounds the minute component up to nearest multiple.
Evaluating
logical tests and returns value TRUE
or FALSE
Logical Functions | |
---|---|
IF | Add IF ELSE Condition. |
AND | TRUE only if both are TRUE. |
OR | FALSE only if both are FALSE. |
NOT | Condition is NOT True. |
SWITCH | Add Multiple Case with conditions. |
COALESCE | Returns non blank evaluations ( IF + ISBLANK ) |
// Returns sum of all non blank values in a column.
COALESCE (
SUM (
'Sales'[Quantity Sold],
),
0
)
Check
the value or data type of all the value or instances and returns TRUE
or FALSE
e.g. Check whether all the rows
of the column are text or numeric or is there any blank row.
Information Functions | |
---|---|
ISBLANK | Check whether a value is blank. |
ISERROR | Check whether a value is an error. |
ISLOGICAL | Check whether a value is a logical value. |
ISNUMBER | Check whether a value is a number. |
ISNONTEXT | Check whether a value is not a text. |
ISTEXT | Check whether a value is text. |
Evaluate
the value to it's correct data type ot format.
Conversion Functions | |
---|---|
CURRENCY(Value) | Evaluate and return as a currency data type. |
FORMAT(Value, Format) | Convert a value to text in specified format. |
DATE(Year, Month, Day) | Return the specified date in datetime format. |
TIME(Hour, Minute, Second) | Return the specified time in datetime format. |
DATEVALUE(DateText) | Convert a date in a text. |
VALUE(Text) | Convert a text format date into a number. |
Date Format (yyyy-mm-dd) =
FORMAT (
"Calendar"[Date],
"yyyy-mm-dd"
)