Forum Discussion

PedroPascual's avatar
PedroPascual
Helper I
2 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
    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. 

OkanAT's avatar
OkanAT
Regular Visitor
2 years ago

Try some correct Syntax:

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

  • PedroPascual's avatar
    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