Power Pivot Principles: The A to Z of DAX Functions – CONTAINS

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

The CONTAINS function

Do you know what CONTAINS contains?  This function returns TRUE if values for all referred columns exist (concurrently); otherwise, the function returns FALSE.

The CONTAINS function employs the following syntax to operate:

• table: this is required.  This represents any DAX expression that returns a table of data
• columnname1, columnname2, …: the first columnname is required; the rest are optional.  This is the name of an existing column (field) using standard DAX syntax.  It cannot be an expression
• value1, value2, …: again, 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 in columnname.  The expression is to be evaluated exactly once and before it is passed to the argument list.

It should be further noted that:

• the arguments columnname and value must come in pairs; otherwise, an error is returned
• columnname must belong to the specified table, or to a table that is related to table
• if columnname refers to a column in a related table then it must be fully qualified; otherwise, an error is returned.  It is probably safest to fully qualify in any case
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Consider the following example, using the Table Data:

The following DAX function

will provide the value FALSE:

This is because although “Sum” is in the Function field and “Product” is in the ‘Business Driver’ field, they are not in the same row of the Table.

If the Table Data were revised so that “Sum” and “Product” are on the same row,

then the ‘Contains Example’ measure would be TRUE instead, viz.

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.