Forum Discussion

ukare1996's avatar
ukare1996
Icon for Helper I rankHelper I
3 years ago
Solved

CALCULATE FILTER Not working

Hi, can someone please help me understand why my query is not working as it should be? I am trying to Count number of rows where the field is not equal to "None" and Blanks are not counted in. The query below seems to only take into account one of the filters. What am I doing wrong please?
 
Monitor 1 - Action taken by customer =
    CALCULATE(
        COUNT('Network protection '[Monitor 1 - Action taken by customer]),
            FILTER('Network protection ', OR('Network protection'[Monitor 1 - Action taken by customer] <> "None", NOT(ISBLANK('Network protection' [Monitor 1 - Action taken by customer])))))
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi ukare1996 

    try like:

    Measure = 
COUNTROWS (
    FILTER (
        'Network protection',
        'Network protection'[Monitor 1]<>"None"
            && 'Network protection'[Monitor 1] <> BLANK() 
    )
)

     

    verified with a simplified dataset:

     

    it worked like:

     

    if you prefer CALCULATE, try like:

    Monitor 1 - Action taken by customer = 
CALCULATE(
    COUNT('Network protection'[Monitor 1]),
    FILTER(
        'Network protection', 
        AND(
            'Network protection'[Monitor 1] <> "None", 
            'Network protection' [Monitor 1] <>BLANK()
        )
    )
)

     

5 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi ukare1996 

    please try

    Monitor 1 - Action taken by customer =
    COUNTROWS (
    FILTER (
    VALUES ( 'Network protection '[Monitor 1 - Action taken by customer] ),
    NOT (
    'Network protection'[Monitor 1 - Action taken by customer]
    IN { "None", BLANK () }
    )
    )
    )

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

      Hi,

      That didnt work sorry. Below is a mock up example of what I need.

      The ideal result I am expecting from here is 2 - with blanks and none excluded.

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        ukare1996 

        please try

        Monitor 1 - Action taken by customer =
        COUNTROWS (
        FILTER (
         'Network protection ',
        NOT (
        'Network protection'[Monitor 1]
        IN { "None", BLANK () }
        )
        )
        )

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi ukare1996 

    try like:

    Measure = 
COUNTROWS (
    FILTER (
        'Network protection',
        'Network protection'[Monitor 1]<>"None"
            && 'Network protection'[Monitor 1] <> BLANK() 
    )
)

     

    verified with a simplified dataset:

     

    it worked like:

     

    if you prefer CALCULATE, try like:

    Monitor 1 - Action taken by customer = 
CALCULATE(
    COUNT('Network protection'[Monitor 1]),
    FILTER(
        'Network protection', 
        AND(
            'Network protection'[Monitor 1] <> "None", 
            'Network protection' [Monitor 1] <>BLANK()
        )
    )
)