Forum Discussion

drrai66's avatar
drrai66
Resolver I
8 years ago
Solved

Need Help on This Formula

Hello Experts,

I need to convert This Tableau Logic to Power BI, But I am Stuck. Not getting it to work. Please Help

 

IF [Status]="TRUE"
THEN
IF CONTAINS(Languages,".net")
OR
CONTAINS(Languages,"Java,")
THEN "True"
ELSE "False"
END
END

 

The Logic First Check if the Status  Is TRUE, Then for those TRUE Values, It Checks , If The Languages Column Contains ".net" OR "Java", If it Does, Then Returns True Else False.

So it is Double IF Statement. How to Write it in POWER BI???

 

Thanks in Advance

Deepak

Need Help
  • drrai66's avatar
    drrai66
    8 years ago

    Thanks Phil and Ashish, I got it Working . This is My Actual Calculation and It is working Perfectly. Thank you Guys for Support.

    Thanks

    Deepak

    Here is Formula I used

     

    IF('Merged Project Data'[Java Script Check 1]>0,IF(ISERROR(SEARCH("Java,",'Merged Project Data'[Language])) && ISERROR(SEARCH(".net",'Merged Project Data'[Language]))&& ISERROR(SEARCH(".NET",'Merged Project Data'[Language]))&& ISERROR(SEARCH("C#",'Merged Project Data'[Language]))&& ISERROR(SEARCH("Microsoft.Net",'Merged Project Data'[Language]))  && ISERROR(SEARCH("Objective-C",'Merged Project Data'[Language]))&& ISERROR(SEARCH("Python",'Merged Project Data'[Language]))&& ISERROR(SEARCH("Ruby",'Merged Project Data'[Language]))&& ISERROR(SEARCH("Siebel",'Merged Project Data'[Language]))&& ISERROR(SEARCH("Proc",'Merged Project Data'[Language]))&& ISERROR(SEARCH("J2EE",'Merged Project Data'[Language])) ,0,'Merged Project Data'[Apps With Platform]))

10 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi drrai66

     

    This calculated column gets close

     

    Column = 
    SWITCH(
        TRUE() ,
        'Table2'[Status] = "TRUE" && 
            (
                SEARCH(".net",'Table2'[Languages],1,0)>0 ||
                SEARCH("Java",'Table2'[Languages],1,0)>0
                ) , "True" ,
        "False"
    )
    • drrai66's avatar
      drrai66
      Resolver I
      8 years ago

      I Think this should be OK. In my actually data, I have 16 OR Statements, so tomorrow I would apply, verify and come back to you with good news.

      Thanks a lot

      Deepak

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        In that instance, I would probably put all your search terms into a table on their own and re-write the calculation to join to the reference table..

    • drrai66's avatar
      drrai66
      Resolver I
      8 years ago

      Sorry Phil,

      The Solution Is not Working, It is Not Searching with in TRUE Values For The Languages we put in the Next Statement, It Is Just Returing TRUE for  Even Those Languages which we have not searched for. In The above Example, it should return TRUE only if Finds .net and Java in Language Column.

      Thanks

      Deepak

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Try this calculated column formula

     

    =IF([Status]="TRUE",IF(OR(ISNUMBER(SEARCH(".Net",[Language])),ISNUMBER(SEARCH(".Java",[Language]))),TRUE(),FALSE()),FALSE())

     

    Hope this helps.

    • drrai66's avatar
      drrai66
      Resolver I
      8 years ago

      This Should also work, Because I was Tryig with ISERROR(Serach type of logic, but got stuck at Last False. I think, I should have written another False as you wrote 2 false(). I was getting it OK, Except False in my Results.

    • drrai66's avatar
      drrai66
      Resolver I
      8 years ago

      Sorry Ashish,

      I think You used ISNUMBER(Search... ,but I am Searching for things like .net, Java, C# in Language Column So I am getting Error. So Solution is not Working

      Thnaks

      Deepak

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        I do not understand.  Show your data and creiteria.  Where is my formula giving the wrong result?