Forum Discussion

PedroPascual's avatar
PedroPascual
Helper I
3 years ago
Solved

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") +     C...
Need Help
  • Anonymous's avatar
    Anonymous
    3 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. 

OkanAT's avatar
OkanAT
Regular Visitor
3 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's avatar
    PedroPascual
    Helper I
    3 years ago

    it is not working,

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