Forum Discussion

SJ25's avatar
SJ25
Resolver 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 &...
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.

MattAllington's avatar
MattAllington
Community 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
    Resolver 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
      Community 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.