DAX Information
Functions
DAX Functions
• A DAX function is a previously created formula that computes
using values passed to it as arguments.
• Function arguments can include a column reference, numbers,
text, constants, references to other formulas or functions, or
logical values like TRUE or FALSE.
• They must be given to the function in a specific order. The
values contained in an argument are subjected to a specific
operation by every function. A DAX formula is capable of
supporting multiple arguments.
Types of DAX Functions
• Date and Time Functions
• Time Intelligence Functions
• Information Functions
• Logical Functions
• Mathematical and Trigonometric Functions
• Statistical Functions
• Text Functions
• Parent-Child functions
• Other functions
• Table functions
• When given a cell or row as an argument, DAX information
functions examine it and inform you of the value’s compatibility
with the expected type.
• If the value you reference contains an error, for instance, the
ISERROR function returns TRUE. Here are the DAX Information
Functions with Output. In order to study these functions, you may
look at the dataset. Below is the screenshot of the dataset:
Contains
• The function either returns true or false depending on whether
the values for all of the referred columns exist or are contained in
those columns. If the values for all the referred columns are not
contained, the function returns false.
• Syntax: CONTAINS(<table>, <column value> <value>[<column value>, <value>]…)
:Example
ContainsString
• Depending on whether one string contains another, this function
returns true or false.
• Syntax: CONTAINSSTRING(<within text>, <find text>)
:Example
ContainsStringExact
• Whether one string contains another is indicated by a return
value of TRUE or FALSE.
• Syntax: CONTAINSSTRINGEXACT(<within_text>, <find_text>)
:Example
IsBlank
• If the value is blank, this function returns true;
• Syntax: ISBLANK(<Value>)
:Example
IsNumber
• This function determines whether a value is a number before
returning either true or false.
• Syntax: ISNUMBER(<value>)
:Example
The DAX Information Functions with examples are shown above.
Here is the list of DAX Information Functions:
Functions Description Syntax
The function either returns true or false depending
CONTAINS(<table>, <column value> <value>[,
CONTAINS on whether the values for all of the referred columns
<column value>, <value>]…)
.exist or are contained in those columns
This function provides a statistics table for each
COLUMNSATIS .column in each table in the model
)(COLUMNSATISTICS
TICS
CONTAINSRO The function returns true if a row value exists or is CONTAINSROW(<tableExpression>,
.contained in a table, or false otherwise <ScalarExpression>[, <ScalarExpression>,…])
W
Depending on whether one string contains another,
CONTAINSSTRING(<within text>, <find text>)
.this function returns true or false
CONTAINSSTR
ING
CONTAINSSTR Whether one string contains another is indicated by CONTAINSSTRINGEXACT(<within_text>,
.a return value of TRUE or FALSE <find_text>)
INGEXACT
Functions Description Syntax
returns the information found in the connection
CUSTOMDATA .string’s CustomData property
)(CUSTOMDATA
If there are exactly One directly filtered values on
HASONEFILTER ColumnName, the function either returns true or HASONEFILTER(<column name>)
.returns false
HASONEVALUE If only one distinct value remains in the context
for ColumnName, the function returns true; HASONEVALUE(<columnName>)
.otherwise, it returns false
ISBLANK ;If the value is blank, this function returns true ISBLANK(<Value>)
If the value is an error, this function returns true;
ISERROR .otherwise, it returns false
ISERROR(<value>)
Functions Description Syntax
This function determines if a value is logical, i.e.
ISLOGICAL TRUE/FALSE: If true, it returns; if false, it returns
ISLOGICAL(<value>)
This function determines whether a value is a
ISNUMBER .number before returning either true or false
ISNUMBER(<value>)
This function determines whether a value is
ISNONTEXT nontext (blanks are treated as non-text), in which ISNONTEXT(<value>)
.case it returns true or false
This function determines whether a value is text
ISTEXT .before returning true or false
ISTEXT(<value>)
Functions Description Syntax
ISAFTER(<scalar_expression>,
a boolean function that behaves like a Start At
<scalar_expression>[, sort_order [,
ISAFTER clause and returns true for a row that satisfies all
<scalar_expression>, <scalar_expression>[,
.of the conditional criteria
sort_order]]…)
ISCROSSFILTE When columnName or another column in the same
or related table is being filtered, this function ISCROSSFILTERED(<TableNameOrColumnName>)
RED .returns TRUE
ISEMPTY .verifies whether a table is empty ISEMPTY(<table_expression>)
If the number is even, it returns TRUE; otherwise,
ISEVEN .it returns FALSE
ISEVEN(number)
when columnName is being directly filtered,
ISFILTERED .returns TRUE
ISFILTERED(<TableNameOrColumnName>)
When a column is the level in a hierarchy of levels,
ISINSCOPE .this function returns true
ISINSCOPE(<columnName>)
Functions Description Syntax
Returns FALSE if the number is even, or TRUE if
ISODD .the number is odd
ISODD(<value>)
ISONORAFTER(<scalar_expression>,
a boolean function that behaves like a Start At
<scalar_expression>[, sort_order [,
ISONORAFTER clause and returns true for a row that satisfies all
<scalar_expression>, <scalar_expression>[,
.of the conditional criteria
sort_order]]…)
Expressions for calculation items use this
ISSELECTEDME information to determine whether the measure
ISSELECTEDMEASURE( M1, M2, … )
ASURE being referenced is one of the ones listed in the
.list of measures
Creates a new column in a SUMMARIZE
expression that returns True if the row has values
ISSUBTOTAL for the subtotal column specified as an argument
ISSUBTOTAL(<columnName>)
.and False otherwise
identifies a value filter as non-visual in a
NONVISUAL .SUMMARIZECOLUMNS expression
NONVISUAL(<expression>)
SELECTEDMEA Used in expressions for calculation items to refer
)(SELECTEDMEASURE
SURE .to the currently referenced measure
DAX Date Functions
• You can develop reliable and high-performance data models in
Power BI by having a fundamental understanding of when and
how to use the DAX functions.
• Your efforts to evaluate data will be more effective the more
familiar you are with DAX functions.
• This is due to your ability to quickly access and analyze
enormous volumes of data from several tables.
• We need to load the data that contains the Date format field.
Here we are taking the records of 50 products sold by a library
supply company. Dataset Used: Sheet1
Dataset Used: Sheet1
Then go to New Table Tab, which would contain the
output column of the following quarries.
DAX Calendar
• Generates a table with a single “Date” column that is made up of
a continuous range of dates. The range of dates is inclusive of
those two dates and extends from the specified start date to the
specified end date.
• Syntax: CALENDAR(<start_date>, <end_date>)
• Example:
• calendar = CALENDAR(05-01-1980,31-01-1990)
Example:
calendar = CALENDAR(05-01-1980,31-01-1990)
DAX Date
• The supplied date is returned in DateTime format by the DATE
function. It creates the corresponding date using the integers
provided as parameters. When the year, month, and day are
determined by formulas, the DATE function is most helpful. The
underlying data, for instance, can include dates in a format that
isn’t accepted as a date, such as YYYYMMDD.
• Syntax: DATE(<year>, <month>, <day>)
• Example :date = DATE(2013,11,18)
Example :date = DATE(2013,11,18)
DAX Datevalue
• Translates a textual date into a date in DateTime format. The
client computer’s locale and date/time settings are used by the
DATEVALUE function to interpret the text value while executing
the conversion.
• Syntax: DATEVALUE(date_text)
• Example: datevalue = DATEVALUE(“25 12 2002”)
Example: datevalue = DATEVALUE(“25 12 2002”)
DAX Datediff
• Returns the number of boundaries between the time frame to compare
dates with. Any of the following values may be the value of the Interval:
• Second,
• Minute,
• Hour,
• Day,
• Week,
• Month,
• Quarter,
• Year
• Syntax: DATEDIFF(<Date1>, <Date2>, <Interval>)
Example: datediff = DATEDIFF(DATE (2001, 07, 01 ),DATE (2002,
12, 31 ),YEAR)
Example: datediff = DATEDIFF(DATE (2001, 07,
01),DATE (2002, 12, 31),MONTH)
DAX Day
• A number between 1 and 31 represents the day of the month.
The date of the day you’re looking for is an argument for the DAY
function.
• Dates can be passed to the method by inputting them in a
DateTime format, using another date function, or using an
expression that yields a date. Additionally, you can type a date in
one of the acceptable date string formats.
• Syntax:DAY(<date>)
• Example day = DAY(“25-12-2002”)
Example day = DAY(“25-12-2002”)
DAX Now
• A datetime format string that contains the current date
and time. When you need to show the current date and
time on a worksheet or compute a number based on the
current date and time and have that value updated
every time you view the worksheet, the NOW function
comes in handy.
• Syntax : NOW()
• Example : now = NOW()
Example : now = NOW()
