Multiple calculate and filters -> need help to optimize

[Anonymous]

Hi everybody,

I'm currently looking for some help in order to optimize my measures (about 25 with the same structure)... I'm sure there are other ways to formulate them but I do not know them. Could someone please help me?

The aim of the formula is to calculate the sum of the amount of an account while excluding several analytical account.

Here is one of them : 

_FF_Immeubles =

calculate(sum('Company'[Amount]);'Company’[Account]=630221;'Company'[DPT]<>"901Admin";'Company'[DPT]<>"911IT";'Company'[DPT]<>"921Fin";'Company'[DPT]<>"931Dir";'Company'[DPT]<>"941Mkt")

+

calculate(sum('Company'[Amount]);'Company’[Account]=630222;'Company'[DPT]<>"901Admin";'Company'[DPT]<>"911IT";'Company'[DPT]<>"921Fin";'Company'[DPT]<>"931Dir";'Company'[DPT]<>"941Mkt")

+

calculate(sum('Company'[Amount]);'Company’[Account]=630223;'Company'[DPT]<>"901Admin";'Company'[DPT]<>"911IT";'Company'[DPT]<>"921Fin";'Company'[DPT]<>"931Dir";'Company'[DPT]<>"941Mkt")

+

calculate(sum('Company'[Amount]);'Company’[Account]=611000;'Company'[DPT]<>"901Admin";'Company'[DPT]<>"911IT";'Company'[DPT]<>"921Fin";'Company'[DPT]<>"931Dir";'Company'[DPT]<>"941Mkt")

+

calculate(sum('Company'[Amount]);'Company’[Account]=611010;'Company'[DPT]<>"901Admin";'Company'[DPT]<>"911IT";'Company'[DPT]<>"921Fin";'Company'[DPT]<>"931Dir";'Company'[DPT]<>"941Mkt")

+

calculate(sum('Company'[Amount]);'Company’[Account]=611020;'Company'[DPT]<>"901Admin";'Company'[DPT]<>"911IT";'Company'[DPT]<>"921Fin";'Company'[DPT]<>"931Dir";'Company'[DPT]<>"941Mkt")

+

calculate(sum('Company'[Amount]);'Company’[Account]=610000;'Company'[DPT]<>"901Admin";'Company'[DPT]<>"911IT";'Company'[DPT]<>"921Fin";'Company'[DPT]<>"931Dir";'Company'[DPT]<>"941Mkt")

+

calculate(sum('Company'[Amount]);'Company’[Account]=613110;'Company'[DPT]<>"901Admin";'Company'[DPT]<>"911IT";'Company'[DPT]<>"921Fin";'Company'[DPT]<>"931Dir";'Company'[DPT]<>"941Mkt")

+

calculate(sum('Company'[Amount]);'Company’[Account]=640030;'Company'[DPT]<>"901Admin";'Company'[DPT]<>"911IT";'Company'[DPT]<>"921Fin";'Company'[DPT]<>"931Dir";'Company'[DPT]<>"941Mkt")

+

calculate(sum('Company'[Amount]);'Company’[Account]>=640210;'Company’[Account]<=640250;'Company'[DPT]<>"901Admin";'Company'[DPT]<>"911IT";'Company'[DPT]<>"921Fin";'Company'[DPT]<>"931Dir";'Company'[DPT]<>"941Mkt")

+

calculate(sum('Company'[Amount]);'Company’[Account]=640280;'Company'[DPT]<>"901Admin";'Company'[DPT]<>"911IT";'Company'[DPT]<>"921Fin";'Company'[DPT]<>"931Dir";'Company'[DPT]<>"941Mkt")

[Anonymous]

Hi @Anonymous 

You  just update your MEasure and ADD your account in IN Clause and DEPT in NOT in Clase.

calculate(sum('Company'[Amount]),filter('Company','Company’[Account] in {630221,630222,...... so on} && NOT('Company'[DPT] in {"921Fin","901Admin",....so on})))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Thanks for your answer... 

I'm going to try it.

But if I have a range of accounts >=600000 and <= 601999 AND also some particular accounts like 602300. How can I do that ? 

Thanks in advance for your answers.

@Anonymous 

[Anonymous · Accepted solution]

 First of all your condition is not right 

accounts >=600000 and <= 601999 AND 602300

There should be OR cuz if you place AND then this condition never evaluate to true.

With OR below measure will give you expected result.

Calculate(Sum(Table_name[COLUMN]),filter(Table_name,Table_name[Account]>=600000 && Table_name[Account]<=601999

||Table_name[Account] in {602300,602301} ))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[cosminc]

Hi,

maybe it can be a better solution to pivot in edit query all these columns to become one.

Hope it helps,

Cosmin