Forum Discussion

sccoleman1189's avatar
sccoleman1189
Icon for Helper I rankHelper I
7 years ago
Solved

Calculate column based on multiple filters

Hello,

 

I am trying to create a measure where I calculate # orders  that were completed where the stations are not equal to or doesnt include a specific value.

 

This is what I currently have:

 

Completed = CALCULATE([# Orders],Production[Comp Status]="COMPLETED")----but this includes all stations

 

I want to not include any station(Production[Home Station1]) that includes "CON" in the station name or station name=MELV-CON, DEVL-CON, LIPO-CON

 

Thanks!

 

 

 

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    sccoleman1189,

     

    Create a measure using DAX formula below:

    Completed = 
CALCULATE (
    [#Orders],
    FILTER (
        Production,
        Production[Comp Status] = "COMPLETED"
            && Production[Home Station1] <> "MELV-CON"
            && Production[Home Station1] <> "DEVL-CON"
            && Production[Home Station1] <> "LIPO-CON"
            && IFERROR ( SEARCH ( "CON", Production[Home Station1] ), -1 ) = -1
    )
)

     

     

    You can also refer to my attachment.

     

    Regards,

    Jimmy Tao

2 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi sccoleman1189

    Can you clarify this:

     

    I want to not include any station(Production[Home Station1]) that includes "CON" in the station name or station name=MELV-CON, DEVL-CON, LIPO-CON

     

    Which ones do you want in and which ones not?

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    sccoleman1189,

     

    Create a measure using DAX formula below:

    Completed = 
CALCULATE (
    [#Orders],
    FILTER (
        Production,
        Production[Comp Status] = "COMPLETED"
            && Production[Home Station1] <> "MELV-CON"
            && Production[Home Station1] <> "DEVL-CON"
            && Production[Home Station1] <> "LIPO-CON"
            && IFERROR ( SEARCH ( "CON", Production[Home Station1] ), -1 ) = -1
    )
)

     

     

    You can also refer to my attachment.

     

    Regards,

    Jimmy Tao