03.Scalar Functions.md

Scalar Functions

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.

Function Syntax

FuntionName ( Argument 1, Argument 2, [Argument 3] )

• Argument specified in square brackets [] are optional arguments.
• Arguments specified without square brackets [] are mandatory 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"
)