Forum Discussion

Tob_P's avatar
Tob_P
Helper V
3 years ago
Solved

Variable Help

Hi there,

 

I have the following measure which I need to adapt to include a Measure in place of the Amount field in the Dyn_OpportunityProduct table..

 

Product Target Filtered Sales =
sumx (
    'tblRep_List',
    VAR BonusStartDate ='tblRep_List'[Product Bonus Start Date]
    RETURN
    CALCULATE(
        SUM('Dyn_OpportunityProduct'[Amount]),
        KEEPFILTERS(
            'CRM: Opportunity'[Date Spec Secured] >= BonusStartDate)))+0
 
It doesn't seem possible to drop the measure in when I omit CALCULATE ( SUM...
 
How can the DAX be amended to allow the Measure to be included?
 
Thanks in advance.
  • PaulOlding's avatar
    PaulOlding
    3 years ago

    Hi Tob_P 

    You could replace the SUM(...) with a measure.  That would give you

    Product Target Filtered Sales =
sumx (
    'tblRep_List',
    VAR BonusStartDate ='tblRep_List'[Product Bonus Start Date]
    RETURN
    CALCULATE(
        [Your Measure],
        KEEPFILTERS(
            'CRM: Opportunity'[Date Spec Secured] >= BonusStartDate)))+0

     I'd suspect performance could be an issue if you have a sizable amount of data.

3 Replies

  • PaulOlding's avatar
    PaulOlding
    Solution Sage
    3 years ago

    Hi Tob_P 

    You could replace the SUM(...) with a measure.  That would give you

    Product Target Filtered Sales =
sumx (
    'tblRep_List',
    VAR BonusStartDate ='tblRep_List'[Product Bonus Start Date]
    RETURN
    CALCULATE(
        [Your Measure],
        KEEPFILTERS(
            'CRM: Opportunity'[Date Spec Secured] >= BonusStartDate)))+0

     I'd suspect performance could be an issue if you have a sizable amount of data.

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Tob_P , if you do calculation inside expression of sumx, you need calculate. else you can have like

     

    measure = VAR BonusStartDate ='tblRep_List'[Product Bonus Start Date]
    RETURN
    CALCULATE(
    SUM('Dyn_OpportunityProduct'[Amount]),
    KEEPFILTERS(
    'CRM: Opportunity'[Date Spec Secured] >= BonusStartDate))

    Product Target Filtered Sales =
    sumx (
    'tblRep_List', [measure]
    )+0

     

    or

     

    Product Target Filtered Sales =
    sumx (
    'tblRep_List', [Any measure]
    )+0

     

    • Tob_P's avatar
      Tob_P
      Helper V
      3 years ago

      amitchandak 

       

      Thanks for coming back to me. I'm afraid the first part of the suggestion will now work as writing...

       

      measure = VAR BonusStartDate ='

       

      ....and this will only allow me to populate a Measure and not a table...