topic Re: Variable Help in DAX Commands and Tips

Variable Help

Tob_P — Mon, 05 Sep 2022 14:54:27 GMT

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.

Re: Variable Help

amitchandak — Mon, 05 Sep 2022 15:59:45 GMT

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

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

Re: Variable Help

Tob_P — Mon, 05 Sep 2022 16:14:52 GMT

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

Re: Variable Help

PaulOlding — Mon, 05 Sep 2022 19:19:03 GMT

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.