Forum Discussion

SJ25's avatar
SJ25
Icon for Resolver I rankResolver I
6 years ago
Solved

Using multiple value filters in Calculate

Hello All,

 

I'm trying to calculate total hours for only certain part numbers. I'm trying to filter them using calculate as below:

 

Reqhrs = Calculate ([Total Hours], New[Part Numbers] = 1111 && 11111)

 

I'm getting the desired result if I filter for just 1 part number but if I enter 2 or more, the result is blank.

 

Any help would be appreciated. Thanks

Need Help
  • MattAllington's avatar
    MattAllington
    6 years ago

    You are using "and" but you should be using "or". Any given product can't be part number 1111 and also be part number 11111


    Here are two options

     

    Reqhrs = Calculate ([Total Hours], New[Part Numbers] = 1111 || 11111)

     

    Reqhrs = Calculate ([Total Hours], New[Part Numbers] in {1111,11111})

     

  • edhans's avatar
    edhans
    6 years ago

    The correct syntax is:

    Reqhrs = Calculate ([Total Hours], New[Part Numbers] = 1111 || New[Part Numbers] = 11111)

    But CALCULATE has a limit on how many boolean filters you can do. You should consider using filter. Much more flexible, and this is what DAX is doing in the background anyway.

    Reqhrs =
CALCULATE (
    [Total Hours],
    FILTER (
        New,
        New[Part Numbers] = 1111
            || New[Part Numbers] = 11111
    )
)

    FILTER will let you filter fields from multiple tables. You cannot do that in CALCULATE without FILTER(). DAX isn't smart enough to translate your boolean logic to include multiple tables.

3 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    6 years ago

    You are using "and" but you should be using "or". Any given product can't be part number 1111 and also be part number 11111


    Here are two options

     

    Reqhrs = Calculate ([Total Hours], New[Part Numbers] = 1111 || 11111)

     

    Reqhrs = Calculate ([Total Hours], New[Part Numbers] in {1111,11111})

     

    • SJ25's avatar
      SJ25
      Icon for Resolver I rankResolver I
      6 years ago

      MattAllington Thanks for your reply. Using the two options you mentioned above is giving me two different results. The first option is giving me the total for all part numbers, the second one seems to be accurate. Upon looking more, is it possible that to use the first option I'll have to repeat the table name after every OR ( || ) sign?

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        The correct syntax is:

        Reqhrs = Calculate ([Total Hours], New[Part Numbers] = 1111 || New[Part Numbers] = 11111)

        But CALCULATE has a limit on how many boolean filters you can do. You should consider using filter. Much more flexible, and this is what DAX is doing in the background anyway.

        Reqhrs =
CALCULATE (
    [Total Hours],
    FILTER (
        New,
        New[Part Numbers] = 1111
            || New[Part Numbers] = 11111
    )
)

        FILTER will let you filter fields from multiple tables. You cannot do that in CALCULATE without FILTER(). DAX isn't smart enough to translate your boolean logic to include multiple tables.