Problem with measure

[Jcho10]

Hi everybody,

I'm beginning with PBI and I've quite difficulties with a simple measure.

I'v just 1 table in my PBI : 'FEC 3 ans'

I'v created a measure to calculate the Turnover of a company.

This measure is called "CHIFFRE D'AFFAIRES"

CHIFFRE D'AFFAIRES =
CALCULATE(
sum('FEC 3 ans'[Solde]);
'FEC 3 ans'[CompteNum]>"70000000";
'FEC 3 ans'[CompteNum]<"70999999";
)
*-1

When a create a table with this measure, I get the correct amount (13 500 555) but the presentation is not correct :

I'v got all the CompteNum instead of only CompteNum which begin with "70"

But If a create the same table with PBI "Filters", I get the correct result and presentation.

I don't understand what is the probleme...

Have you an idea ?

Thank you for your help

Sincerely

[OwenAuger]

Hi @Jcho10

The problem you've encountered is due to the the fact that filter arguments provided to CALCULATE overwrite the filter context.

This becomes apparent when your table filters by one of the columns contained in the filter arguments (CompteNum in this case).

You just need to rewrite your measure so that the filter context for the CompteNum is intersected with these arguments, for example:

CHIFFRE D'AFFAIRES =
CALCULATE (
    SUM ( 'FEC 3 ans'[Solde] );
    AND ( 'FEC 3 ans'[CompteNum] > "70000000"; 'FEC 3 ans'[CompteNum] < "70999999" );
    VALUES ( 'FEC 3 ans'[CompteNum] )
)
    * -1

or

CHIFFRE D'AFFAIRES =
CALCULATE (
    SUM ( 'FEC 3 ans'[Solde] );
    KEEPFILTERS (
        AND ( 'FEC 3 ans'[CompteNum] > "70000000"; 'FEC 3 ans'[CompteNum] < "70999999" )
    )
)
    * -1

Owen 🙂

[Jcho10]

Thank you for your answer,

But in reality, my measure has 3 parameters in the CALCULATE function.

How could I use your measure with 3 parameters ?

CHIFFRE D'AFFAIRES N = 
CALCULATE(
sum('FEC 3 ans'[Solde]);
'FEC 3 ans'[CompteNum]>"70";
'FEC 3 ans'[CompteNum]<"71";
'FEC 3 ans'[Exercice]=date(2016;06;30)
)

I tried this one but It doesnt work :

CHIFFRE D'AFFAIRES =
CALCULATE (
    SUM ( 'FEC 3 ans'[Solde] );
    KEEPFILTERS (
        AND ( 'FEC 3 ans'[CompteNum] > "70"; 'FEC 3 ans'[CompteNum] < "71" ;'FEC 3 ans'[Exercice]=date(2016;06;30))
    )
)
    * -1

Could you help me ?

Thanks you

[OwenAuger · Accepted solution]

@Jcho10

No problem. Actually there are a few restrictions which I think are useful to list:

• Each boolean filter argument provided to CALCULATE can refer to only one column
  So, for example, you can have
  AND ( 'FEC 3 ans'[CompteNum] > "70"; 'FEC 3 ans'[CompteNum] < "71" )
  but not
  AND ( 'FEC 3 ans'[CompteNum] > "70"; 'FEC 3 ans'[Exercice]=date(2016;06;30) )
• AND takes only two arguments (for more than that two have to use A && B && C...)
• KEEPFILTERS(...) can take only a single expression as its argument (not immediately an issue but thought I'd mention anyway)

I think in this case VALUES will be just fine rather than KEEPFILTERS.

You can write your measure like:

CHIFFRE D'AFFAIRES =
CALCULATE (
    SUM ( 'FEC 3 ans'[Solde] );
    AND ( 'FEC 3 ans'[CompteNum] > "70"; 'FEC 3 ans'[CompteNum] < "71" );
    VALUES ( 'FEC 3 ans'[CompteNum] );
    'FEC 3 ans'[Exercice] = DATE ( 2016; 06; 30 )
)
    * -1