Open In App

Power BI – DAX Information Functions

Improve
Improve
Like Article
Like
Save
Share
Report

Data Analysis Expressions, or DAX, are formulas or expressions that are used for calculations and data analysis. These expressions consist of a number of different functions, operators, and constants that are all evaluated together as a single formula to produce the desired results (value or values). In BI tools like Power BI, DAX formulas are very helpful because they enable data analysts to make the best use possible of the data sets they already have.

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

The different DAX function types are described above. A DAX function is a previously created formula that computes using values passed to it as arguments. We now look at DAX Information Functions,

DAX Information 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:

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 :

applying-contains-function

CONTAINS – DAX Information Function

ContainsString

Depending on whether one string contains another, this function returns true or false. 

Syntax: CONTAINSSTRING(<within text>, <find text>)

Example:

applying-containsstring-function

CONTAINSSTRING– DAX Information Function

ContainsStringExact

Whether one string contains another is indicated by a return value of TRUE or FALSE.      

Syntax: CONTAINSSTRINGEXACT(<within_text>, <find_text>)

Example:

applying-containsstringexact-function

CONTAINSSTRINGEXACT – DAX Information Function

IsBlank

If the value is blank, this function returns true;    

Syntax: ISBLANK(<Value>)

Example:

applying-isblank-function

ISBLANK – DAX Information Function

IsNumber

This function determines whether a value is a number before returning either true or false.                   

Syntax:  ISNUMBER(<value>)

Example:

applying-isnumber-function

ISNUMBER – DAX Information Function

The DAX Information Functions with examples are shown above.

Here is the list of DAX Information Functions:

Functions

Description

Syntax

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.

CONTAINS(<table>, <column value> <value>[, <column value>, <value>]…)

      COLUMNSATISTICS 

This function provides a statistics table for each column in each table in the model.

COLUMNSATISTICS()

CONTAINSROW

The function returns true if a row value exists or is contained in a table, or false otherwise.

CONTAINSROW(<tableExpression>, <ScalarExpression>[, <ScalarExpression>,…])

                                                    CONTAINSSTRING                                  

Depending on whether one string contains another, this function returns true or false.

CONTAINSSTRING(<within text>, <find text>)

CONTAINSSTRINGEXACT    

Whether one string contains another is indicated by a return value of TRUE or FALSE.

CONTAINSSTRINGEXACT(<within_text>, <find_text>)

CUSTOMDATA

returns the information found in the connection string’s CustomData property.

CUSTOMDATA()

HASONEFILTER

If there are exactly One directly filtered values on ColumnName, the function either returns true or returns false.

HASONEFILTER(<column name>)

HASONEVALUE 

If only one distinct value remains in the context for ColumnName, the function returns true; otherwise, it returns false.

HASONEVALUE(<columnName>)

ISBLANK

If the value is blank, this function returns true; 

ISBLANK(<Value>)

ISERROR

If the value is an error, this function returns true; otherwise, it returns false.

ISERROR(<value>)

ISLOGICAL 

This function determines if a value is logical, i.e. TRUE/FALSE: If true, it returns; if false, it returns

ISLOGICAL(<value>)

ISNUMBER 

This function determines whether a value is a number before returning either true or false.

ISNUMBER(<value>)

ISNONTEXT

This function determines whether a value is nontext (blanks are treated as non-text), in which case it returns true or false.

ISNONTEXT(<value>)

ISTEXT

This function determines whether a value is text before returning true or false.

ISTEXT(<value>)

ISAFTER

a boolean function that behaves like a Start At clause and returns true for a row that satisfies all of the conditional criteria.

ISAFTER(<scalar_expression>, <scalar_expression>[, sort_order [, <scalar_expression>, <scalar_expression>[, sort_order]]…)

ISCROSSFILTERED

When columnName or another column in the same or related table is being filtered, this function returns TRUE.

ISCROSSFILTERED(<TableNameOrColumnName>)

ISEMPTY

verifies whether a table is empty.

ISEMPTY(<table_expression>)

ISEVEN

If the number is even, it returns TRUE; otherwise, it returns FALSE.

ISEVEN(number)

ISFILTERED 

when columnName is being directly filtered, returns TRUE.

ISFILTERED(<TableNameOrColumnName>)

ISINSCOPE

When a column is the level in a hierarchy of levels, this function returns true.

ISINSCOPE(<columnName>)

ISODD

Returns FALSE if the number is even, or TRUE if the number is odd.

ISODD(<value>)

ISONORAFTER

a boolean function that behaves like a Start At clause and returns true for a row that satisfies all of the conditional criteria.

ISONORAFTER(<scalar_expression>, <scalar_expression>[, sort_order [, <scalar_expression>, <scalar_expression>[, sort_order]]…)

ISSELECTEDMEASURE

Expressions for calculation items use this information to determine whether the measure being referenced is one of the ones listed in the list of measures.

ISSELECTEDMEASURE( M1, M2, … )

ISSUBTOTAL

Creates a new column in a SUMMARIZE expression that returns True if the row has values for the subtotal column specified as an argument and False otherwise.

ISSUBTOTAL(<columnName>)

NONVISUAL

identifies a value filter as non-visual in a SUMMARIZECOLUMNS expression.

NONVISUAL(<expression>)

SELECTEDMEASURE

Used in expressions for calculation items to refer to the currently referenced measure.

SELECTEDMEASURE()

Conclusion

This was a brief overview of Power BI’s DAX and DAX Information Functions. Power BI is a very potent tool created by Microsoft that aids in the formulation of important business decisions and encourages the expansion of the company. By using these features, our information will be converted into a format that is simple to understand.



Last Updated : 16 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads