Forum Discussion

aliciab425's avatar
aliciab425
Regular Visitor
4 years ago
Solved

Custom Column for Multiple IF & And Statements

The below formula is cauclated in Excel. I would like to see if this would be easily calculated in Power BI, however, I currently have AST1, AST2, and AST3, I need to add AST4, AST5, and AST6. Is this something easily formulated in Power BI?

 

Here is the current Excel formula with illustrationfor AST1, AST2, and AST3. I do not have an illustration with the additional AST4, AST5, and AST6 because the formula becomes more complex. 

 

IF(AND(A2="BLANK1",B2="BLANK2",C2="BLANK3"),"Investigate",IF(AND(A2=B2,A2=C2),A2,IF(AND(A2<>"BLANK1",B2="BLANK2",C2="BLANK3"),A2,IF(AND(A2=C2,B2="BLANK2"),A2,IF(AND(A2<>C2,A2<>"BLANK1",C2<>"BLANK3",B2="BLANK2"),"House",IF(AND(A2<>"BLANK1",B2<>"BLANK2",A2<>B2,C2="BLANK3"),"House",IF(AND(A2="BLANK1",B2<>"BLANK2",C2="BLANK3"),B2,IF(AND(A2="BLANK1",B2<>"BLANK2",B2<>C2),"House",IF(AND(A2="BLANK1",B2="BLANK2",C2<>"BLANK3"),C2,IF(AND(A2<>"BLANK1",B2<>"BLANK2",C2<>"BLANK3",A2<>B2,B2<>C2),"House",IF(AND(A2="BLANK1",B2=C2),B2,"ERROR")))))))))))

 

AST1AST2AST3Expected ResultLine Item FormulaComp Territory
BLANK1BLANK2BLANK3InvestigateInvestigateInvestigate
T1T1T1T1T1T1
T1BLANK2BLANK3T1T1T1
T1BLANK2T1T1T1T1
T1BLANK2T2HouseHouseHouse
T1T2BLANK3HouseHouseHouse
BLANK1T2BLANK3T2T2T2
BLANK1T2T1HouseHouseHouse
BLANK1BLANK2T2T2T2T2
T1T2T3HouseHouseHouse
BLANK1T2T2T2T2T2

 

Thank you in advance!

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    If I understand your logic correctly, this should work:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvJx9PM2VNKBMIxgDGOlWJ1opRCQBIyAC+BUCZfAqjrECMlMNO1wV+CXgJqL6WYMs0OwageqigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AST1 = _t, AST2 = _t, AST3 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each
        let
            L = List.Distinct(List.Select({[AST1],[AST2],[AST3]}, each not Text.StartsWith(_, "BLANK")))
        in
            if List.Count(L) = 0 then "Investigate"
            else if List.Count(L) > 1 then "House"
            else L{0},
        type text)
in
    #"Added Custom"

    This takes the list of values in AST1, AST2, AST3 and removes the items starting with "BLANK". Then it removes any duplicates and returns different values depending on how many items remain.

1 Reply

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    If I understand your logic correctly, this should work:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvJx9PM2VNKBMIxgDGOlWJ1opRCQBIyAC+BUCZfAqjrECMlMNO1wV+CXgJqL6WYMs0OwageqigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AST1 = _t, AST2 = _t, AST3 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each
        let
            L = List.Distinct(List.Select({[AST1],[AST2],[AST3]}, each not Text.StartsWith(_, "BLANK")))
        in
            if List.Count(L) = 0 then "Investigate"
            else if List.Count(L) > 1 then "House"
            else L{0},
        type text)
in
    #"Added Custom"

    This takes the list of values in AST1, AST2, AST3 and removes the items starting with "BLANK". Then it removes any duplicates and returns different values depending on how many items remain.