Forum Discussion

FelippeAzevedo7's avatar
FelippeAzevedo7
Helper I
2 years ago
Solved

Error formula

 

 

Does anyone know why this formula doesn't work correctly, if I have it right and it works similarly in excel?
I need to validate if 3 columns have "Yes" and if this occurs the process is "Yes", if there is any no it will be "No".

Thank you

 

Correct: Yes or No = IF(
    AND(
        [CKT Populated?] = "Yes",
        [PTT Name Populated?] = "Yes",
        [PTT Ticket Populated?] = "Yes"),
    "Yes",
    "No"
)
 
another example that is not working
 
Correct: Yes or No = VAR col1 = SELECTEDVALUE('Sheet1'[CKT Populated?])  ;
VAR col2 = SELECTEDVALUE('Sheet1'[PTT Name Populated?])  ;
VAR col3 = SELECTEDVALUE('Sheet1'[PTT Ticket Populated?])  ;

IF(
  AND(col1 = "YES", col2 = "YES", col3 = "YES"),
  "YES","No")
 
Many tks all
  • aduguid's avatar
    aduguid
    2 years ago

    I would create the following calculated columns to check each column value by itself.

    Check CKT = IF([CKT Populated?] = "Yes", "Yes", "No")
Check PTT Name = IF([PTT Name Populated?] = "Yes", "Yes", "No")
Check PTT Ticket = IF([PTT Ticket Populated?] = "Yes", "Yes", "No")

     

  • xifeng_L's avatar
    xifeng_L
    2 years ago

    Hi FelippeAzevedo7 ,

     

    The AND function in DAX supports only two arguments. (https://learn.microsoft.com/en-us/dax/and-function-dax)

     

     

    So you can change the formula to the following form:

     

    Correct: Yes or No = IF(
    AND(
        AND(
            [CKT Populated?] = "Yes",
            [PTT Name Populated?] = "Yes"
        ),
        [PTT Ticket Populated?] = "Yes"
    ),
    "Yes",
    "No"
)

     

    Or use the logical operators &&:

     

    Correct: Yes or No = 
IF(
    [CKT Populated?] = "Yes" && 
        [PTT Name Populated?] = "Yes" &&
            [PTT Ticket Populated?] = "Yes",
    "Yes",
    "No"
)

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~

     

  • FelippeAzevedo7's avatar
    FelippeAzevedo7
    2 years ago

    Hello xifeng_L

     

    How are you?

    I will use the formula to solve my problem.

    Many tks for your support

5 Replies

  • xifeng_L's avatar
    xifeng_L
    Super User
    2 years ago

    Hi FelippeAzevedo7 ,

     

    The AND function in DAX supports only two arguments. (https://learn.microsoft.com/en-us/dax/and-function-dax)

     

     

    So you can change the formula to the following form:

     

    Correct: Yes or No = IF(
    AND(
        AND(
            [CKT Populated?] = "Yes",
            [PTT Name Populated?] = "Yes"
        ),
        [PTT Ticket Populated?] = "Yes"
    ),
    "Yes",
    "No"
)

     

    Or use the logical operators &&:

     

    Correct: Yes or No = 
IF(
    [CKT Populated?] = "Yes" && 
        [PTT Name Populated?] = "Yes" &&
            [PTT Ticket Populated?] = "Yes",
    "Yes",
    "No"
)

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~

     

    • FelippeAzevedo7's avatar
      FelippeAzevedo7
      Helper I
      2 years ago

      Hello xifeng_L

       

      How are you?

      I will use the formula to solve my problem.

      Many tks for your support

    • FelippeAzevedo7's avatar
      FelippeAzevedo7
      Helper I
      2 years ago

      Hi

       

      Tested and worked fine!!

       

      Correct: Yes or No =
      IF(
      [CKT Populated?] = "Yes" &&
      [PTT Name Populated?] = "Yes" &&
      [PTT Ticket Populated?] = "Yes",
      "Yes",
      "No"
      )

      Tks a lot

  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago

    I would create the following calculated columns to check each column value by itself.

    Check CKT = IF([CKT Populated?] = "Yes", "Yes", "No")
Check PTT Name = IF([PTT Name Populated?] = "Yes", "Yes", "No")
Check PTT Ticket = IF([PTT Ticket Populated?] = "Yes", "Yes", "No")

     

    • FelippeAzevedo7's avatar
      FelippeAzevedo7
      Helper I
      2 years ago

      Hello

       

      Many tks for the feedback.

      I'm still not as good with power BI as I am with excel.
      Thanks