cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mloudenot
Regular Visitor

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

 

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

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

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Stachu
Community Champion
Community Champion

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

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

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] Smiley LOL

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors