Variable Help

[Tob_P]

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 · Accepted solution]

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]

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

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