Forum Discussion

gauravnarchal's avatar
gauravnarchal
Post Prodigy
4 years ago
Solved

Need help with TRUE function

Hello  -  I want to create a measure to get the result as true or false for the below 2 conditions.

 

  1. If the location starts with SIE and contains locations RUI or KLT orAKL then return true else false

 

  1. If the location starts and ends with SIE and contains locations RUI & KLT or AKL then return true else false

 

Data 

 

Table1

Location
SIE*KLT*JTR/MIL
SIE*KLT*MIL
MIL*KLT*MIL
KLT*MIL*KLT
SIE*KLT*RUI*MIL
SIE*RUI*KLT*MIL
SIE*JUY*KLT*MIL
SIE*JUY*KLT*MIL*SIE
SIE*RUI*KLT*MIL*SIE
SIE*PLI*SIE
SIE*QWE*SIE
SIE*AKL/SIE
SIE*AKL
SIE*AKL*PLO
SIE*RUI/KLT*MIL
SIE*JUY*KLT

 

Thanks

Need Help
Show and Tell
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, gauravnarchal 

     

    You can try the following function to write measure.

     

    Measure1 = LEFT ( MAX('Table'[Location]), 3 ) = "SIE"
    && (
        CONTAINSSTRING ( MAX('Table'[Location]), "RUI" )
            || CONTAINSSTRING ( MAX('Table'[Location]), "KLT" )
            || CONTAINSSTRING ( MAX('Table'[Location]), "AKL" )
)

    Measure2 = 
LEFT ( MAX ( 'Table'[Location] ), 3 ) = "SIE"
    && RIGHT ( MAX ( 'Table'[Location] ), 3 ) = "SIE"
    && (
        CONTAINSSTRING ( MAX ( 'Table'[Location] ), "RUI" )
            || CONTAINSSTRING ( MAX ( 'Table'[Location] ), "KLT" )
            || CONTAINSSTRING ( MAX ( 'Table'[Location] ), "AKL" )
)

     

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • mattww's avatar
    mattww
    Responsive Resident
    4 years ago

    gauravnarchal 

     

    Something along the lines of a Custom Column in Power Query like

     

    if Text.StartsWith([Location],"SIE") and (Text.Contains([Location],"RUI") or Text.Contains([Location],"KLT") or Text.Contains([Location],"AKL")) then true
    else false

     

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    You can do this with LEFT and CONTAINSSTRING.

    LEFT ( Table1[Location], 3 ) = "SIE"
    && (
        CONTAINSSTRING ( Table1[Location], "RUI" )
            || CONTAINSSTRING ( Table1[Location], "KLT" )
            || CONTAINSSTRING ( Table1[Location], "AKL" )
    )

     

     

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, gauravnarchal 

     

    You can try the following function to write measure.

     

    Measure1 = LEFT ( MAX('Table'[Location]), 3 ) = "SIE"
    && (
        CONTAINSSTRING ( MAX('Table'[Location]), "RUI" )
            || CONTAINSSTRING ( MAX('Table'[Location]), "KLT" )
            || CONTAINSSTRING ( MAX('Table'[Location]), "AKL" )
)

    Measure2 = 
LEFT ( MAX ( 'Table'[Location] ), 3 ) = "SIE"
    && RIGHT ( MAX ( 'Table'[Location] ), 3 ) = "SIE"
    && (
        CONTAINSSTRING ( MAX ( 'Table'[Location] ), "RUI" )
            || CONTAINSSTRING ( MAX ( 'Table'[Location] ), "KLT" )
            || CONTAINSSTRING ( MAX ( 'Table'[Location] ), "AKL" )
)

     

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.