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

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

3 Replies

  • 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