Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 ) )
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 ) )
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |