Power Pivot Principles: The A to Z of DAX Functions – CONTAINSROW
5 July 2022
In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at CONTAINSROW.
The CONTAINSROW function
Similar to the CONTAINS function, CONTAINSROW returns TRUE if there is at least one row in a given table where all columns have specified values. Where CONTAINS requires only certain field(s) to be specified, CONTAINSROW requires you to specify the values in order for all fields in the table.
The CONTAINSROW function employs the following syntax to operate:
CONTAINSROW(table, value1[, value2, …])
- table: this is required. This represents any DAX expression that returns a table of data
- value1, value2, …: the first value is required, the rest are optional. This is any DAX expression that returns a single scalar value that is to be sought for each field in the table.
It should be further noted that:
- CONTAINSROW and the IN operator are functionally equivalent. Many prefer the latter, as it is often regarded as a simpler syntax
- The syntax for IN is as follows:
scalar expression IN table expression
(scalar expression1, scalar expression2, …) IN table expression
- The number of scalar expression values must match the number of columns in the table
- NOT IN is not an operator in DAX. To perform the logical negation of the IN operator, put NOT in front of the entire expression, e.g.
NOT [Colour] IN { "Red", "Yellow", "Blue" }
- Unlike the equals (=) operator, the IN operator and the CONTAINSROW function perform strict comparisons. For example, the BLANK value does not match zero [0].
Consider the following example, using the Table Data:
The following DAX function
=CONTAINSROW(Data, "Sum", "Product")
will provide the value TRUE:
This is because “Sum” is in the first field and “Product” is in the second field for one of the rows of the Table.
Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.
