Forum Discussion

mkpado's avatar
mkpado
Icon for Helper I rankHelper I
8 years ago
Solved

DAX Horizontal Lookoup

I am looking to write a formula in DAX that will look with a records and deterime if a value exists in any of the columns associated with that record. For example lets say I have 5 records with 3 Checks that I am running accross each record. If the word "Error" appears in any of the columns within a record, a fourth column (Pass / Fail) should read "Fail" if not it should be read "Pass". See table below for a visual.

 

In excel I would write this formula with a match funcction (i.e. =if(match("Error",Range,0)>0,"Fail","Pass")) but there are no equivalent funtions in DAX that I can find. The only function that I have found that performs a lookup is LOOKUPVALUE but form what I can tell this function only looks up vertically in a column and not accross a row (i.e. record).

 

Please let me know if anyone has a workaround for this.

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    There should be no use of a single |

     

    The double || is the operator for OR in logical tests.  There is a function called OR (..) that takes parameters.

3 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi mkpado

     

    Please add the following calculated column to your table

     

    Pass / Fail = 
    IF (
        'Table1'[Check 1] = "Error" ||  
        'Table1'[Check 2] = "Error" || 
        'Table1'[Check 2] = "Error" , 
        -- THEN --
        "Fail" , 
        -- ELSE --
        "Pass" 
        )
      • Phil_Seamark's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        There should be no use of a single |

         

        The double || is the operator for OR in logical tests.  There is a function called OR (..) that takes parameters.