Symbol for "contains" in a formula

[PedroPascual]

Hi community,

i have this formula:

IF (

    AND (

        AND (

            '_CombinedMasterFile'[Identity ] = " DUCTS",

            '_CombinedMasterFile'[ Type] = " Exhaust "

            || '_CombinedMasterFile'[ Type] = "Supply "

        ),

        '_CombinedMasterFile'[Category] = "Types"

            || '_CombinedMasterFile'[Category] = "Fittings"

            || '_CombinedMasterFile'[Category] = "Accessories"              

            || '_CombinedMasterFile'[Category] = "Terminals"  

            || '_CombinedMasterFile'[Category] = "Flex "            

    ),

    "YES",

    "NO"

)

And I need to change the = "value" to "contains "value"

Can anyone help me?

[Anonymous · Accepted solution]

Hi  @PedroPascual , 

This might be better illustrated with an example:

6M-602DUCC CRITICAL HVAC DUCTS =

IF (

    AND (

        AND (

            MAX('_CombinedMasterFile'[Identity Data.Workset]) = "6M-602DUCC CRITICAL HVAC DUCTS",

            MAX ('_CombinedMasterFile'[Mechanical.System Type] ) IN {"QUA-PSY-EXA-GEN-001 Exhaust Air","QUA-PSY-EXA-GEN-001 Supply Air"}

        ),

        MAX('_CombinedMasterFile'[RevitFamilyCategory] ) IN {"Duct Types","Duct Fittings","Duct Accessories","Air Terminals","Flex Duct Types"}

         

    ),

    "YES",

    "NO"

)

 

Its use can be interpreted as being equal to any one of this array, not really a contained.

 

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[PedroPascual]

Thanks @Anonymous 

what i do not understand is why with your formula the results are NO, when it should be Yes.

Thanks in advance

[rsbin]

@PedroPascual ,

Syntax is:
CONTAINSSTRING( <Within Text>, <Find Text> )
https://learn.microsoft.com/en-us/dax/containsstring-function-dax

Hope this is what you are looking for.

Regards,

[PedroPascual]

but i would not like to change the formula, just change the = to contains

[rsbin]

@PedroPascual ,

IF (
    AND (
        AND (
            CONTAINSSTRING('_CombinedMasterFile'[Identity ], "DUCTS", )
 etc....

[Anonymous]

Hi @PedroPascual ,

Based on your description, I'm guessing you're wondering if contaion has any symbols like "=" that can stand for equals.And here may be the answer you are looking for, you can check the results as follows:

Measure =
IF (
    AND (
        AND (
            MAX ( '_CombinedMasterFile'[Identity] ) = "DUCTS",
            MAX ( '_CombinedMasterFile'[type] ) IN { "Exhaust", "DUCTS" }
        ),
        MAX ( '_CombinedMasterFile'[Category] )
            IN { "Types", "Fittings", "Accessories", "Terminals", "Flex" }
    ),
    "YES",
    "NO"
)

Please feel free to correct me and provide more information if I have misunderstood you!

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[PedroPascual]

Hi @Anonymous 

thanks for your response i thought was the solution but it does not work for me.

This is based on your formula:

6M-602DUCC CRITICAL HVAC DUCTS =

IF (

    AND (

        AND (

            MAX('_CombinedMasterFile'[Identity Data.Workset]) = "6M-602DUCC CRITICAL HVAC DUCTS",

            MAX ('_CombinedMasterFile'[Mechanical.System Type] ) IN {"-PSY-EXA-","-PSY-SUA-"}

        ),

        MAX('_CombinedMasterFile'[RevitFamilyCategory] ) IN {"Air Terminals","Duct"}

    ),

    "YES",

    "NO"

)

and the results are:

but previously this formula worked:

I want to reduce the formula with a action "contains" instead of the "="

[Anonymous]

Hi @PedroPascual ，

"in" means that there are these fields, which can be interpreted as being equal to one of them, and if you explicitly say contains, it requires the use of DAX. or you can create conditional columns through Power Query, a very simple way.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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