Need Help on This Formula

[drrai66]

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

[Ashish_Mathur]

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]

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]

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

[drrai66 · Accepted solution]

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]))

[drrai66]

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.

[Phil_Seamark]

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]

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

[drrai66]

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]

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]

Actually, Status and Languages are Column of Same Table and I seek To Create Another Calculated Column in the Same Table, So I think, Your Logic would work.