Create a filter in a Measure

[PedroPascual]

Hi community, i have a problem with my formula.
i have create a measure based on new columns:

Yes=

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-501]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-502]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-503 ]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-504]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-505]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-506]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-508P]  = "YES")

I need to create a filter in this measure based on a specific value of a column, this value shall be CONTAINS.

the formula would be something like this:

IF( CONTAINSSTRING('_CombinedMasterFile'[Column1], "value1"), CALCULATE(

is it possible?

Regards

[v-binbinyu-msft · Accepted solution]

Hi @PedroPascual ,

Please try below dax formula:

Yes =
VAR _a =
    COUNTROWS (
        FILTER (
            ALL ( '_CombinedMasterFile' ),
            '_CombinedMasterFile'[5E-501] = "YES"
                && CONTAINSSTRING ( '_CombinedMasterFile'[Column1], "value1" )
        )
    )
VAR _b =
    COUNTROWS (
        FILTER (
            ALL ( '_CombinedMasterFile' ),
            '_CombinedMasterFile'[5E-502] = "YES"
                && CONTAINSSTRING ( '_CombinedMasterFile'[Column1], "value1" )
        )
    )
VAR _c =
    COUNTROWS (
        FILTER (
            ALL ( '_CombinedMasterFile' ),
            '_CombinedMasterFile'[5E-503] = "YES"
                && CONTAINSSTRING ( '_CombinedMasterFile'[Column1], "value1" )
        )
    )
VAR _d =
    COUNTROWS (
        FILTER (
            ALL ( '_CombinedMasterFile' ),
            '_CombinedMasterFile'[5E-504] = "YES"
                && CONTAINSSTRING ( '_CombinedMasterFile'[Column1], "value1" )
        )
    )
VAR _e =
    COUNTROWS (
        FILTER (
            ALL ( '_CombinedMasterFile' ),
            '_CombinedMasterFile'[5E-505] = "YES"
                && CONTAINSSTRING ( '_CombinedMasterFile'[Column1], "value1" )
        )
    )
VAR _f =
    COUNTROWS (
        FILTER (
            ALL ( '_CombinedMasterFile' ),
            '_CombinedMasterFile'[5E-506] = "YES"
                && CONTAINSSTRING ( '_CombinedMasterFile'[Column1], "value1" )
        )
    )
VAR _g =
    COUNTROWS (
        FILTER (
            ALL ( '_CombinedMasterFile' ),
            '_CombinedMasterFile'[5E-508P] = "YES"
                && CONTAINSSTRING ( '_CombinedMasterFile'[Column1], "value1" )
        )
    )
RETURN
    _a + _b + _c + _d + _e + _f + _g

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Miasm1]

Hii,

Use the SEARCH function to detect "value1" in Column1. Wrap with ISNUMBER to verify if the substring exists. Then, incorporate this into an IF statement. If "value1" exists in the current row of Column1, your measure's calculations are executed. If not, it returns a default value like BLANK(). Ensure the context of MIN fits your table structure; adjust if necessary.

[PedroPascual]

Thanks  @Miasm1 , how would yo do it properly with the formula?

[PedroPascual]

@Miasm1 can you help with this?
can you formulate the formula yo explained?

[OkanAT]

Does this then work?
CALCULATE(COUNTROWS('_CombinedMasterFile'), '_CombinedMasterFile'[5E-501] = "YES" && CONTAINSSTRING('_CombinedMasterFile'[Column1], "value1")) for each line. 

[PedroPascual]

No,

As with this formula, i am unable to calculate and sum the 7 variables :

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-501]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-502]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-503 ]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-504]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-505]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-506]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-508P]  = "YES")

[OkanAT]

Try some correct Syntax:

CALCULATE(
COUNTROWS('_CombinedMasterFile'),
FILTER(
'_CombinedMasterFile',
'_CombinedMasterFile'[6M-601] = "YES" &&
CONTAINSSTRING('_CombinedMasterFile'[Data], "6M")
)
)

[PedroPascual]

think that i need to calulate the total a mount of YES in here:

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-501]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-502]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-503 ]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-504]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-505]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-506]  = "YES") +

    CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-508P]  = "YES")

so is not working with your solution

[OkanAT]

You add the filter on Calculate after your expression separated by a comma. 

so:  CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[5E-501]  = "YES"), CONTAINSSTRING('_CombinedMasterFile'[Column1], "value1").

Docs

[PedroPascual]

it is not working,

The syntax for ',' is incorrect. (DAX( CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[6M-601] = "YES"), CONTAINSSTRING('_CombinedMasterFile'[Data], "6M"))).