QA comparicion between Values

[PedroPascual]

Hi Community,

hopefuly, you can help me with this formula.

i need to do a QA based on values from different columns and tables.

table1
column1	column2	column3		QA
value1	value2	value3
house	car	dog		YES
house	car	dog		YES
house	car	cat		NO

I already have the formula for the same table:

Comparision2Value =

IF (

    AND (

        OR (

            Table1[Column2] = "Car",

            Table1[Column3] = "Dog"

        ),

        Table1[Column1] = "House"

    ),

    "Yes",

    "No"

)

This formula works perfectly. The main strategy is if the value from column 1 has the values in the same raw in two different columns then yes.

My problem now is to do the same but with more values from different tables.

table1	table 2	table 3
column1	column2	column3	Column1	Column1		QA
value1	value2	value3	value4	value5
house	car	dog	pencil	apple		YES
house	car	dog	pencil	lemon		NO
house	car	cat	book	orange		NO

The main strategy is if the value from Table 1 column1 has in the rest of the columns the specific values then yes, otherwise no

Can anyone help me??

[Anonymous]

Hi @PedroPascual ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) Click "transform data" to enter the power query, add an index column for each table, and merge into one table.

(3)We can create a calculated column.

QA = IF('Table1'[column1]="value1",BLANK(), IF('Table1'[Table2.column2]="car" && 'Table1'[Table3.column3]="dog" && 'Table1'[Table4.column4]="pencil" && 'Table1'[Table5.Column5]="apple","YES","NO"))

(4) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[PedroPascual]

@Anonymous, thanks very much for your reply. your response is correct.
however, I did wrong my proposal.
Let me re-formulate the issue I have, a little bit more clear.
the conditional value shall be based in at least 3 conditional values:
the value "house" from Column1 must contain in column2 "cat" and/or "dog" and/or "rabbit" and/or "lion" and in column3 "book" and/or "pencil" otherwise is NO;

Column1	Column2	Column3	QA
House	cat	book	YES
House	dog	book	YES
House	cat	pencil	YES
House	dog	pencil	YES
House	rabbit	window	NO
House	lion	door	NO

and it could be a possibility that a fourth column with another rule will be used.

it is clear??

thanks in advance

[Anonymous · Accepted solution]

Hi @PedroPascual ,

We can create a calculated column.

QA =
IF (
    AND (
        AND (
            'Table'[Column1] = "House",
            'Table'[Column2] = "cat"
                || 'Table'[Column2] = "dog"
                || 'Table'[Column2] = "rabbit"
                || 'Table'[Column2] = "lion"
        ),
        'Table'[Column3] = "book"
            || 'Table'[Column3] = "pencil"
    ),
    "YES",
    "NO"
)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[PedroPascual]

@Anonymous Wonderful. it is working properly. thanks.

last question, what about if I have information in different tables??
does not work.

QA =
IF (
AND (
AND (
'Table1'[Column1] = "House",
'Table2'[Column2] = "cat"
|| 'Table2'[Column2] = "dog"
|| 'Table2'[Column2] = "rabbit"
|| 'Table2'[Column2] = "lion"
),
'Table1'[Column3] = "book"
|| 'Table1'[Column3] = "pencil"
),
"YES",
"NO"
)

[Anonymous]

Hi @PedroPascual ,

If he is in different tables, refer to Message 4, click on "Transform Data" to access the Power Query, add index columns for each table, and merge them into one table.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Ahmedx]

it's not clear what you want.
can you explain why there is no in next line:
house machine dog pencil lemon NO

[PedroPascual]

The rule I need to create in the formula is:

if the value1 in Table1 and Column1 contains the other values "car", "dog", "pencil" & "apple"  is a "Yes", otherwise is a "NO".

Other Values can be in the same table or in different tables, but with a relation with Value1