Forum Discussion

PedroPascual's avatar
PedroPascual
Helper I
3 years ago
Solved

QA comparicion between Values

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
column1column2column3 QA
value1value2value3  
housecardog YES
housecardog YES
housecarcat 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.

table1table 2table 3  
column1column2column3Column1Column1 QA
value1value2value3value4value5  
housecardogpencilapple YES
housecardogpencillemon NO
housecarcatbookorange 

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??

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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. 

7 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

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

    • PedroPascual's avatar
      PedroPascual
      Helper I
      3 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      PedroPascual
      Helper I
      3 years ago

      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;

      Column1Column2Column3QA
      HousecatbookYES
      HousedogbookYES
      Housecat pencilYES
      HousedogpencilYES
      HouserabbitwindowNO
      HouseliondoorNO


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

      it is clear??

      thanks in advance

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.