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
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.
Comments