Create a filter in a Measure

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

Need Help

Anonymous

2 years ago

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.

10 Replies

OkanAT
Regular Visitor
2 years ago
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
  Helper I
  2 years ago
  it is not working,
  The syntax for ',' is incorrect. (DAX( CALCULATE(COUNTROWS('_CombinedMasterFile'),'_CombinedMasterFile'[6M-601] = "YES"), CONTAINSSTRING('_CombinedMasterFile'[Data], "6M"))).
OkanAT
Regular Visitor
2 years ago
Try some correct Syntax:
CALCULATE(
COUNTROWS('_CombinedMasterFile'),
FILTER(
'_CombinedMasterFile',
'_CombinedMasterFile'[6M-601] = "YES" &&
CONTAINSSTRING('_CombinedMasterFile'[Data], "6M")
)
)
- PedroPascual
  Helper I
  2 years ago
  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
Regular Visitor
2 years ago
Does this then work?
CALCULATE(COUNTROWS('_CombinedMasterFile'), '_CombinedMasterFile'[5E-501] = "YES" && CONTAINSSTRING('_CombinedMasterFile'[Column1], "value1")) for each line.
- PedroPascual
  Helper I
  2 years ago
  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")
Miasm1
New Member
2 years ago
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
  Helper I
  2 years ago
  Thanks Miasm1 , how would yo do it properly with the formula?
  - PedroPascual
    Helper I
    2 years ago
    Miasm1 can you help with this?
    can you formulate the formula yo explained?

Anonymous

Not applicable

2 years ago

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.

Forum Discussion

Create a filter in a Measure

10 Replies

Related content

Need help creating a measure

Help to create DAX Measure

Error in create measure for filter

Dynamic measure filtering

Creating a measure that is the value of another measure but ignores a filter