Forum Discussion

mloudenot's avatar
mloudenot
Regular Visitor
7 years ago
Solved

Help on DAX calculate/complex filtering on multiple columns

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's avatar
    Stachu
    7 years ago

    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?

    Column1Column2
    A1
    B2.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
    )
)

     

2 Replies

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

    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?

    Column1Column2
    A1
    B2.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's avatar
      mloudenot
      Regular Visitor
      7 years ago

      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] :smileylol:

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