Multiple columns CONTAINS criteria, check if column contains any

[Anonymous]

For a project I currently have two tables, Table1 and Table2, with at least column A and B that can contain any value.

I am trying to evaluate multiple criteria per row.

Criteria 1: Both column A and B of Table1 and Table2 contain identical values on a row to their respective columns. I solved this using CONTAINS(Table1,Table1[A],Table2[A],Table1[B],Table2[B])

Criteria 2: Column A containts an identical value in Table1 and Table2 AND Column B is empty in Table 1 and column B contains any value in Table2. I would expect a solution to come in the form of IF(ISBLANK(Table1[B]), CONTAINS(Table1,Table1[A],Table2[A],Table2[B],"*"))

However this does not produce the desired result as it does not evaluate the "*" it seems. How would one find multiple criteria. A MWE of the tables and the expected outcome is provided underneath.

Table 1

A	B	Expected Outcome
Req-032	X	True
Req-0652	Y	True
Req-0652	X	False
0042		True
Req-024		False

Table 2

A	B
Req-032	X
Req-0652	Y
Req-0652	Z
0042	A
Req-024

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try to create a measure like this:

result = 
VAR _isB_notBlank =
    NOT ( ISBLANK ( MAX ( 'Table1'[B] ) ) )
VAR _T2_BinA =
    SUMMARIZE ( FILTER ( ALL ( Table2 ), 'Table2'[A] = MAX ( 'Table1'[A] ) ), [B] )
VAR _if =
    IF (
        _isB_notBlank,
        IF ( MAX ( 'Table1'[B] ) IN _T2_BinA, "True", "False" ),
        MAXX ( _T2_BinA, [B] ) <> BLANK ()
    )
RETURN
    _if

result:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

@v-angzheng-msft  Awesome, this works.

Although I don't quite understand why this works as a measure instead of a column.

[Greg_Deckler]

@Anonymous Use a COUNTROWS of INTERSECT

[Anonymous]

@Greg_Deckler 

Unfortunately I can not use INTERSECT as the tables are not limited to Column A and B, but also included C,D, etc.

How would using COUNTROWS provide the expected outcome? I am thinking of CALCULATE(COUNTROWS(Table2),FILTER(NOT(ISBLANK(Table1[B]))) > 0 but currently I have no relationship between these two Tables so that does not work unfortunately.

[Greg_Deckler]

@Anonymous Let me be more clear and I also think I understand the problem differently.

Create a table variables using SELECTCOLUMNS to select just your 1 column, column B in Table A. You can then get the current value of Column B in context in your other table in a different VAR. You can then use CONTAINS or the IN operator.