Forum Discussion

vijayvizzu's avatar
vijayvizzu
Helper III
5 years ago
Solved

Double Filter

I have created a measure to count the carry overs (previous month values on selected month). The results are working fine, but i want to apply one more filter to exclude the rows which contains the words "ABC" or "DEF".

 

Below is the current measure which gives me total count, including ABC and DEF

How can i fitler the values excluding the above criteria

 
CarryOver =
VAR CarryoverCount =
CALCULATE(
COUNT('Forwarder Details'[Carry Over]),
PREVIOUSMONTH( calTable[Date] )
)

RETURN
CarryoverCount
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi vijayvizzu ，

    Please update the formula of measure as below and check whether it can get the correct result. You can find the attachment for the details.

    Measure =
    VAR CarryoverCount =
    CALCULATE (
    COUNT ( 'Forwarder Details'[Carry Over] ),
    PREVIOUSMONTH ( calTable[Date] ),
    FILTER (
    ALL ( 'Forwarder Details' ),
    NOT ( 'Forwarder Details'[Custom] IN { "ABC", "DEF" } )
    )
    )
    RETURN
    CarryoverCount

    Best Regards

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    vijayvizzu , Try like

     

    CarryOver =
    VAR CarryoverCount =
    CALCULATE(
    COUNT('Forwarder Details'[Carry Over]),
    PREVIOUSMONTH( calTable[Date] ),
    filter('Forwarder Details', not('Forwarder Details'[column] in {"ABC" , "DEF"}))
    )

    • vijayvizzu's avatar
      vijayvizzu
      Helper III
      5 years ago

      amitchandak Thanks for your support,  i modified the measure, but it doesn't gives any values (blank)

       

      VAR CarryoverCount =
      CALCULATE(
      COUNT('Forwarder Details'[Carry Over]),
      PREVIOUSMONTH( calTable[Date] ),
      FILTER(
      'Forwarder Details',
      NOT( 'Forwarder Details'[Custom] in {"ABC","DEF"})
      )
      )

      RETURN
      CarryoverCount
      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        vijayvizzu ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.