Advanced DAX - Scalar Functions
So today we are going to learn what are scalar function in advanced Data Analysis Expression.
DAX
Data Analysis Expressions (DAX) is a formula expression language used in Analysis Services, Power BI, and Power Pivot in Excel. DAX formulas include functions, operators, and values to perform advanced calculations and queries on data in related tables and columns in tabular data models.
This article provides only a basic introduction to the most important concepts in DAX.
SCALAR FUNCTIONS
▸Scalar function in a function that returns one single value. This value can be of any data type; Date, Numeric, Text, etc. But it is always one single value. One of the most basic and simple functions in this category is SUM.
►Categories :
1. Aggregation Functions
2. Rounding Functions
3.Information Functions
4.Conversion Functions
5.Logical Functions
let's look on these functions
1. Aggregation Functions
▸Functions that can be used to dynamically aggregate values within a column
▸Aggregation functions calculate a (scalar) value such as count, sum, average, minimum, or maximum for all rows in a column or table as defined by the expression.
▸Functions :
1. SUM
2. AVERAGE
3. MIN/MAX
4. COUNT
5. PRODUCT
6. ITERATOR
1. SUM
▹Adds all the numbers in a column.
▹Syntax : SUM ( < Column_Name > )
▹Example : total = SUM(Film[RunTimeMinutes])
2. AVERAGE
▹Returns the average (arithmetic mean) of all the numbers in a column.
▹If you want to find the average of an expression that evaluates to a set of numbers, use the AVERAGEX function instead
▹Syntax : AVERAGE(<column>)
▹Example : Average = AVERAGE(Film[RunTimeMinutes])
3. MIN/MAX
▸MIN
▹Returns the smallest value in a column, or between two scalar expressions.
▹Syntax : MIN(<column>)
MIN(<expression1>, <expression2>)
The column in which you want to find the smallest value.
Any DAX expression which returns a single value.
Example : Minimum = MIN(Film[RunTimeMinutes])
▸MAX
▹Returns the largest value in a column, or between two scalar expressions.
▹Syntax : MAX(<column>)
MAX(<expression1>, <expression2>)
The column in which you want to find the smallest value.
Any DAX expression which returns a single value.
Example : Maximum = MAX(Film[RunTimeMinutes])
4. COUNT
▸COUNT
▹Counts the number of rows in the specified column that contain non-blank values.
▹Syntax : COUNT(<column>)
▹Example : COUNT = COUNT(Film[OscarWins])
▸COUNTBLANK
▹Counts the number of blank cells in a column
▹Syntax : COUNTBLANK(<column>)
▹Example : coun_ax = COUNTAX(Film,[Blanks]==0)
▸COUNTROWS
▹Counts the number of rows in the specified table, or in a table defined by an expression.
▹Syntax : COUNTROWS([<table>])
▹Example : count_rows = COUNTROWS('Orders’)
▸COUNTAX
▹Counts non-blank results when evaluating the result of an expression over a table.
▹Syntax : COUNTAX(<table>,<expression>)
▹Example : coun_ax = COUNTAX(Film,[Blanks]==0)
▸COUNTX
▹Counts the number of rows that contain a number or an expression that evaluates to a number, when evaluating an expression over a table.
▹Syntax : COUNTX(<table>,<expression>)
▹Example : countx= COUNTX(Product,[ListPrice])
▸DISTINCTCOUNT / DISTINCTCOUNTNOBLANK
▹Counts the number of distinct values in a column.
▹Syntax : DISTINCTCOUNT(<column>)
▹Example : count_distinct = DISTINCTCOUNT(Film[RunTimeMinutes])
5. PRODUCT
▸PRODUCT
▹Returns the product of the numbers in a column.
▹Syntax : PRODUCT(<column>)
▹Example : product = PRODUCT(Film[BudgetDollars])
▸PRODUCTX
▹Returns the product of an expression evaluated for each row in a table.
▹Syntax : PRODUCTX(<table>, <expression>)
▹Example : productx = [PresentValue] * PRODUCTX( AnnuityPeriods, 1+[FixedInterestRate] )
2. Rounding Functions
▸Functions that can be used to round values to different levels of precision.
▸Functions :
1.FLOOR
2.TRUNC
3.ROUND
4.FIXED
5.CEILING
6.INT
1.FLOOR
▹The floor function gives the greatest nearest value that is less than or equal to the specified value. That means the resultant value will be less than the specified value.
▹Syntax : FLOOR(<number>, <significance>)
▹Example : floor = FLOOR(456.97653452 , 4) returns 456
2.TRUNC
▹Truncates a number to an integer by removing the decimal, or fractional, part of the number
▹Syntax : TRUNC(<number>,<num_digits>)
▹Example : Trunc = TRUNC(456.97653452 , 4) returns 456.98
3.ROUND
▸ROUND
▹Rounds a number to the specified number of digits.
▹Syntax : ROUND(<number>, <num_digits>)
▹Example : Round = ROUND(456.97653452 , 4) returns 456.98
▸ROUNDDOWN
▹Rounds a number down, toward zero.
▹Syntax : ROUNDDOWN(<number>, <num_digits>)
▹Example : Round_down = ROUNDDOWN(456.97653452 , 4) returns 456.98
▸MROUND
▹Returns a number rounded to the desired multiple.
▹Syntax : MROUND(<number>, <multiple>)
▹Example : M_round = MROUND(456.97653452 , 4) returns 456
4.FIXED
▹Rounds a number to the specified number of decimals and returns the result as text. You can specify that the result be returned with or without commas.
▹Syntax : FIXED(<number>, <decimals>, <no_commas>)
▹Example : fixed = FIXED(987.66524,3,”no_commas”) returns 987
5.CEILING
▹Rounds a number up, to the nearest integer or to the nearest multiple of significance
▹Syntax : CEILING(<number>, <significance>)
▹Example : ceiling = CEILING(456.97653452 , 4) returns 460
6.INT
▹Rounds a number down to the nearest integer.
▹Syntax : INT(<number>)
▹Example : Int = INT(456.97653452) returns 456
3.Information Functions
▸Functions that can be used to analyze the data type or output of an expression.
▸For example, the ISERROR function returns TRUE if the value that you reference contains an error.
▸Functions :
1. ISBLANK
2. ISERROR
3. ISNONTEXT
4. ISLOGICAL
5. ISNUMBER
6. ISTEXT
1. ISBLANK
▹Checks whether a value is blank, and returns TRUE or FALSE.
▹Syntax : ISBLANK(<value>)
▹Example : is_blank = ISBLANK('AllMeasures'[Blanks]) returns TRUE.
2. ISERROR
▹Checks whether a value is an error, and returns TRUE or FALSE.
▹Syntax : ISERROR(<value>)
▹Example : is_Error = ISERROR('AllMeasures'[COUNT]) returns FALSE.
3. ISNONTEXT
▹Checks if a value is not text (blank cells are not text), and returns TRUE or FALSE.
▹Syntax : ISNONTEXT(<value>)
▹Example : is_nontext = ISNONTEXT('AllMeasures'[COUNT]) returns TRUE.
4. ISLOGICAL
▹Checks whether a value is a logical value, (TRUE or FALSE), and returns TRUE or FALSE.
▹Syntax : ISLOGICAL(<value>)
▹Example : is_logical = ISLOGICAL('AllMeasures'[Average]) returns TRUE.
5. ISNUMBER
▹Checks whether a value is a number, and returns TRUE or FALSE.
▹Syntax : ISNUMBER(<value>)
▹Example : is_number = ISNUMBER('AllMeasures'[COUNT]) returns TRUE.
6. ISTEXT
▹Checks if a value is text, and returns TRUE or FALSE.
▹Syntax : ISTEXT(<value>)
▹Example : is_text = ISTEXT('AllMeasures'[Rows]) returns TRUE.
4.Conversion Functions
▸Conversion Functions is used to convert one type of datatype in to other one.
▸Functions that are used to force a specific data type conversion.
▸Functions :
1. CURRENCY
2. FORMAT
3. DATE
4. DATEVALUE
5.TIME
6.VALUE
1. CURRENCY
▹Evaluates the argument and returns the result as currency data type.
▹Syntax : CURRENCY(<value>)
▹Example : currency = CURRENCY('AllMeasures'[COUNT])
2. FORMAT
▹Converts a value to text according to the specified format.
▹Syntax : FORMAT(<value>, <format_string>[, <locale_name>])
▹Example : format = FORMAT('AllMeasures'[total] , "Standard")
3. DATE
▹Returns the specified date in datetime format.
▹Syntax : DATE(<year>, <month>, <day>)
▹Example : date = DATE(2022,06,13)
4. DATEVALUE
▹Converts a date in text format to a date in datetime format.
▹Syntax : DATEVALUE(date_text)
▹Example : date_value = DATEVALUE("12-june-2022")
5.TIME
▹Converts hours, minutes, and seconds given as numbers to a time in datetime format.
▹Syntax : TIME(hour, minute, second)
▹Example : time = TIME(11 ,06, 32)
6.VALUE
▹Converts a text string that represents a number to a number.
▹Syntax : VALUE(<text>)
▹Example : value = VALUE('AllMeasures'[Average])
5.Logical Functions
▸Functions for returning information about values in a conditional expression.
▸Functions :
1. IF
2. AND
3. OR
4. NOT
5. TRUE
6. FALSE
7. COALESCE
8. SWITCH
1. IF
▹Checks a condition, and returns one value when it's TRUE, otherwise it returns a second value.
▹Syntax : IF(<logical_test>, <value_if_true>[, <value_if_false>])
▹Example : if = IF(1000 < 500 ,"Low", "high") returns FALSE.
2. AND
▹Checks whether both arguments are TRUE, and returns TRUE if both arguments are TRUE. Otherwise returns false.
▹Syntax : AND(<logical1>,<logical2>)
▹Example : and = IF(AND(10 > 9, -10 < -1), "All true", "One or more false" ) returns TRUE.
3. OR
▹Checks whether one of the arguments is TRUE to return TRUE. The function returns FALSE if both arguments are FALSE.
▹Syntax : OR(<logical1>,<logical2>)
▹Example : or = IF(OR(10>4,1>4) , "any one is correct" , "both are incorrect") returns “any one is correct ”.
4. NOT
▹Changes FALSE to TRUE, or TRUE to FALSE.
▹Syntax : NOT(<logical>)
▹Example : not = NOT(IF (400>10 ,"true","false")) returns “false”.
5. TRUE
▹Returns the logical value TRUE.
▹Syntax : TRUE()
▹Example : = IF(SUM('InternetSales_USD'[SalesAmount_USD]) >200000, TRUE(), FALSE()) returns TRUE
6. FALSE
▹Returns the logical value FALSE.
▹Syntax : FALSE()
▹Example : = IF(SUM('InternetSales_USD'[SalesAmount_USD]) >200000, TRUE(), FALSE()) returns FALSE
7. COALESCE
▹Returns the first expression that does not evaluate to BLANK. If all expressions evaluate to BLANK, BLANK is returned.
▹Syntax : COALESCE(<expression>, <expression>[, <expression>]…)
▹Example : = COALESCE(SUM(FactInternetSales[SalesAmount]), 0)
▹Returns the sum of all values in the SalesAmount column in the FactInternetSales table, or 0. This can be used to convert BLANK values of total sales to 0.
8. SWITCH
▹Evaluates an expression against a list of values and returns one of multiple possible result expressions.
▹Syntax : SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
Example : = SWITCH([Month], 1, "January", 2, "February", 3, "March", 4, "April" , 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December" , "Unknown month number" )
if we call switch(2) then it returns February.
some examples of these functions.
1. Aggregation functions
2. Rounding functions
3. Information functions
4. Conversion functions
5. Logical functions
- VIDHISHA LIMBOLA
- Jun, 18 2022
