Using multiple value filters in Calculate

[SJ25]

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

[MattAllington · Accepted solution]

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})

[MattAllington · Accepted solution]

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]

@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 · Accepted solution]

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.