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 on whether the values for all of CONTAINS(<table>, <column value> <value>[,
CONTAINS the referred columns exist or are contained <column value>, <value>]…)
in those columns.

COLUMNS This function provides a statistics table

COLUMNSATISTICS()
ATISTICS for each column in each table in the model.

The function returns true if a row value

CONTAINSROW(<tableExpression>,
CONTAINSROW exists or is contained in a table, or false
<ScalarExpression>[, <ScalarExpression>,…])
otherwise.

CON Depending on whether one string contains

another, this function returns true or CONTAINSSTRING(<within text>, <find text>)
TAINSSTRING false.
 Functions Description Syntax

returns the information found in the

CUSTOMDATA connection string’s CustomData property.
CUSTOMDATA()

If there are exactly One directly filtered

HASONEFILTER values on ColumnName, the function either HASONEFILTER(<column name>)
returns true or returns false.

If only one distinct value remains in the

HASONEVALUE context for ColumnName, the function HASONEVALUE(<columnName>)
returns true; otherwise, it returns false.

If the value is blank, this function

ISBLANK returns true;
ISBLANK(<Value>)

If the value is an error, this function

ISERROR returns true; otherwise, it returns false.
ISERROR(<value>)
 Functions Description Syntax

This function determines if a value is

ISLOGICAL logical, i.e. TRUE/FALSE: If true, it ISLOGICAL(<value>)
returns; if false, it returns

This function determines whether a value

ISNUMBER is a number before returning either true ISNUMBER(<value>)
or false.

This function determines whether a value

is nontext (blanks are treated as non-
ISNONTEXT text), in which case it returns true or
ISNONTEXT(<value>)
false.

This function determines whether a value

ISTEXT is text before returning true or false.
ISTEXT(<value>)
 Functions Description Syntax

a boolean function that behaves like a ISAFTER(<scalar_expression>,

Start At clause and returns true for a row <scalar_expression>[, sort_order [,
ISAFTER that satisfies all of the conditional <scalar_expression>, <scalar_expression>[,
criteria. sort_order]]…)

ISCROSSFILTERE When columnName or another column in the

same or related table is being filtered, ISCROSSFILTERED(<TableNameOrColumnName>)
D this function returns TRUE.

ISEMPTY verifies whether a table is empty. ISEMPTY(<table_expression>)

If the number is even, it returns TRUE;

ISEVEN otherwise, it returns FALSE.
ISEVEN(number)

when columnName is being directly

ISFILTERED filtered, returns TRUE.
ISFILTERED(<TableNameOrColumnName>)

When a column is the level in a hierarchy

ISINSCOPE of levels, this function returns true.
ISINSCOPE(<columnName>)
 Functions Description Syntax

Returns FALSE if the number is even, or

ISODD TRUE if the number is odd.
ISODD(<value>)

a boolean function that behaves like a ISONORAFTER(<scalar_expression>,

Start At clause and returns true for a <scalar_expression>[, sort_order [,
ISONORAFTER row that satisfies all of the conditional <scalar_expression>,
criteria. <scalar_expression>[, sort_order]]…)

Expressions for calculation items use

ISSELECTEDMEAS this information to determine whether the
ISSELECTEDMEASURE( M1, M2, … )
URE measure 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
ISSUBTOTAL has values for the subtotal column ISSUBTOTAL(<columnName>)
specified as an argument and False
otherwise.

identifies a value filter as non-visual

NONVISUAL in a SUMMARIZECOLUMNS expression.
NONVISUAL(<expression>)

SELECTEDMEASUR Used in expressions for calculation items

to refer to the currently referenced SELECTEDMEASURE()
E 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()