Power BI – DAX Information Functions
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:
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 |
---|---|---|
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.