• Marek Vavrovic

Information functions, part one

DAX information functions look at the cell or row that is provided as an argument and tells you whether the value matches the expected type. For example, the ISERROR function returns TRUE if the value that you reference contains an error.


Data I will work with





HASONEFILTER

CONTAINSSTRING

CONTAINSROW

CONTAINS


Returns TRUE if there exists at least one row where all columns have specified values.


syntax:

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


this function is not case sensitive, it does not work with FIND, SEARCH functions.




Example 1> CONTAINS

I want to check if there is a value of 92 in the Sale column

using one condition

using two conditions

CONTAINSROW


syntax

CONTAINSROW(<tableExpr>, <scalarExpr>[, <scalarExpr>, …])


Example 1> calculate sum of the sales for the stores :AAA,BBB


CR = CALCULATE(SUM(tblStore[Sale]), CONTAINSROW({"AAA","BBB"},tblStore[Store]))


alternatively use KEEPFILTERS function, returns the same result


Sales for AAA BBB = CALCULATE(SUM(tblStore[Sale]),KEEPFILTERS(tblStore[Store] in {"AAA","BBB"}))

Example 2> calculate NOT CONTAINSROW


I want to calculate the sales for all the stores beside AAA, BBB

CONTAINSSTRING


CONTAINSSTRING vs CONTAINSSTRINGEXACT


  • CONTAINSSTRING is not case-sensitive.

  • You can use ? and * wildcard characters. Use ~ to escape wildcard characters.

  • CONTAINSSTRINGEXACT is case-sensitive.

Example 1 > find expression toy using CONTAINSSTRING function

Example 2> find expression toy using CONTAINSSTRINGEXACT function


HASONEFILTER


HASONEFILTER vs HASONEVALUE vs ISFILTERED vs ISCROSSFILTERED


HAONEFILTER returns TRUE when the number of directly filtered values on columnName is one; otherwise returns FALSE


HASONEVALUE returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise is FALSE


ISFILTERED returns TRUE when columnName is being filtered directly. If there is no filter on the column or if the filtering happens because a different column in the same table or in a related table is being filtered then the function returns FALSE.


ISCROSSFILTERED returns TRUE when columnName or another column in the same or related table is being filtered


DATA MODEL


I will be filtering Customers[Name] with all the four functions.


HASONEFILTER = HASONEFILTER(Customers[Name])

HASONEVALUE = HASONEVALUE(Customers[Name])

ISCROSSFILTERED = ISCROSSFILTERED(Customers[Name])

ISFILTERED = ISFILTERED(Customers[Name])


If nothing have been filtered all the functions return FALSE.


Customers[Name] is a direct filter, because the Name column is from table Customers.

Customers[ID] is an indirect filter, because it is from the same table as Customers[Name] column.

Country[Country] is cross filter, because it is form different table.

scenario 1


One direct filter applied: All the functions return TRUE


Multiple direct filters applied : HASONEFILTER & HASONEVALUE return FALSE because more than one filter was used.


ISCROSSSFILTERED returns TRUE because the filter comes from related table. All tables are related, means this function will, in this data model. always returning TRUE.


ISFILTERED returns TRUE because I used a direct filter.

scenario 2


HASONEFILTER returned FALSE. This function needs 1 direct filter to return TRUE in other cases returns FALSE.

HASONEVALUE if there is more than 1 filter applied this function returns FALSE. If you will use just 1 direct or just 1 indirect or just 1 cross filter, this function returns TRUE.

ISFILTERED if one or multiple directs filters are applied this function will return TRUE. Even if you use an additional indirect or cross filter together with an direct filter, one or multiple, this function returns TRUE.

ISCROSSFILTERED returned TRUE because all the filters come from related tables.

scenario 3


As you can see on the top table I have just one direct filter applied.




6 views0 comments

Recent Posts

See All

Index

Information functions Filter functions Table manipulation functions Grouping and summarizing Time intelligence functions OPENINGBALANCEYEAR OPENINGBALANCEYEAR(<Expression>,<Dates> [,<Filter>][,<YearEn