Help on DAX calculate/complex filtering on multiple columns

[mloudenot]

Hello,

I am quite new to Powerpivot so please be kind 🙂

I am trying to create a new metric "# Orders" with different filters:

* on column "KPI", sum only the KPI called "# Orders"

* filter OUT (do not add in the sum) the combination of 2 filters on 2 other columns: the value "1" on column "Is a partner order" and the value "1" on column "Flag partner". Here I mean that having one of them true is fine, the values I want to exclude are the ones where BOTH filters combined are true (1 AND 1).

I have tried:

# Orders:= calculate ( [Sum of Value] , 'table 1'[KPI] = "# Orders" , filter ( 'table1', NOT ( value('table 1'[Is a partner order])=1 && 'table1'[Flag partner]=1 )))

But it doesn't give out the result I am expecting.

Could someone please help me write it correctly?

Thanks in advance for any help or advice you might have!

Manon

[Stachu · Accepted solution]

as far as I can tell the syntax is perfectly fine for what you're trying to achieve, with just 2 typos - you use

'table 1' and later 'table1' (no space) - I assume this is actually the same table, correct?

can you add sample 'table1' (in format that can be copied to PowerBI) from your model with anonymised data?

Column1	Column2
A	1
B	2.5

you could rewrite measure like this, but the result is the same for me (I just used 'table1')

Measure =
CALCULATE (
    SUM ( 'table1'[Sum of Value] ),
    'table1'[KPI] = "# Orders",
    FILTER (
        'table1',
        'table1'[Is a partner order] <> 1
            || 'table1'[Flag partner] <> 1
    )
)

[mloudenot]

Thanks a lot Stachu! In the end my formula worked, it was just a question of summing the right column [Sum of Value2] instead of [Sum of Value] 

Your formula was another way to see it and also gave the same result! Thank you!