Forum Discussion

Jcho10's avatar
Jcho10
Frequent Visitor
9 years ago
Solved

Problem with measure

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 compa...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    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
OwenAuger's avatar
OwenAuger
Super User
9 years ago

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's avatar
    Jcho10
    Frequent Visitor
    9 years ago

    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's avatar
      OwenAuger
      Super User
      9 years ago

      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