DAX contains / row syntax

[dicken]

I have sort of addressed this problem on excel forum ;

https://www.excelforum.com/office-365/1414306-dax-contains-for-or-conditions.html#post5885588

Can someone explain the following I have a few examples of CONTAINS / ROW
These are calculated columns;

A  CONTAINS( Table1,Table1[Name],"tom")
B  CALCULATE(  CONTAINSROW( VALUES( Table1[Name]) , "tom") ) 
C    CONTAINSROW({"tom"}, Table1[Name] )

All these produce a correct true false for the row, but why does B need calculate ?

But if I write a measure for containsrow it then needs ;

CONTAINSROW( VALUES(Table1[Name]), "tom")

even though most of the online examples follow a pattern ;

CONTAINSROW ( { "Red", "Blue", "Yellow" }, Product[Color] )

when I try

CONTAINSROW( {"tom"}, Table1[Name])

it just get an error ?

Richard.

 

[Matt Allington]

You haven’t mentioned the name of the table you’re adding the calc column to, nor any relationships in your model. If I assume it’s Table1, then the issue is the use of values() in B. Values() produces a new table that is not included in the row context of the calc column in Table1. Calculate forces context transition which takes the row context from the calculated column and applies it as a filter context to the values(). Values then has a single value, hence it works. Without calculate, the values() contains all unique values in column Table1[name]. If you remove values(), then table1[name] has a single value by nature of the calculated column row context hence the calculate function is not needed. This assumes the cc is added to Table1

 

[dicken]

There are no relationships, its a single column table with a name in each cell,
if you don't use Values then

CONTAINSROW( Table2[Names] ,"Tom")

= Errors for each row of the calculated column,

CONTAINSROW( VALUES( Table2[Names]), "Tom")

= True and then calculate = true just for the correct rows. ?


Any views on how the measures work as to which needs to

CONTAINSROW( VALUES(Table1[Name]), "tom")
CONTAINSROW( {"tom"}, Table1[Name])

Most of the articles seem to the second syntax and it's used in MS docs, I just get an error.