Forum Discussion

PedroPascual's avatar
PedroPascual
Icon for Helper I rankHelper 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. 

Miasm1's avatar
Miasm1
New Member
3 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's avatar
    PedroPascual
    Icon for Helper I rankHelper I
    3 years ago

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

    • PedroPascual's avatar
      PedroPascual
      Icon for Helper I rankHelper I
      3 years ago

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