Forum Discussion

CarlosPelay's avatar
CarlosPelay
Frequent Visitor
1 year ago
Solved

Error Using a Dax Measure as a constant

Hi I have a dash with a number of measures filtered by the current fiscal year. 

 

I'd like to save the value of the current fiscal year in a Dax measure and then use that measure as a sort of constant to filter all my other measures.  That way I only have to update the value of the one constant measure every year instead of all the other measures.

 

For example:

 

__CURRENT CAMPAIGN YR = "FY25"
 
and then
 
Appeals_Total$_SPONSORSHIP = CALCULATE(SUM('Final_RE_Gift Detail'[Appeal Split Amount]),'Final_RE_Gift Detail'[Appeal]="BBFC Sponsorship",'Final_RE_Gift Detail'[Campaign]=[__CURRENT CAMPAIGN YR])
 
But this gives me the following error:
 

 

However, this works:

 

Appeals_Total$_SPONSORSHIP = CALCULATE(SUM('Final_RE_Gift Detail'[Appeal Split Amount]),'Final_RE_Gift Detail'[Appeal]="BBFC Sponsorship",'Final_RE_Gift Detail'[Campaign]="FY24")
 

Any suggestions as to how I can implement this correctly?

 

Thank you.

  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from lbendlin.

     

    Hi CarlosPelay,

     

    As lbendlin says, when we try to use a measure as a filter in another measure, a syntax error occurs because the measure cannot be calculated as a fixed constant value.

    To solve this problem, we can use a variable in the second measure to store the value of __CURRENT CAMPAIGN YR.

    Please use the following DAX:

     

    Appeals_Total$_SPONSORSHIP = 
VAR _a=[__CURRENT CAMPAIGN YR]
RETURN
CALCULATE(SUM('Final_RE_Gift Detail'[Appeal Split Amount]),'Final_RE_Gift Detail'[Appeal]="BBFC Sponsorship",'Final_RE_Gift Detail'[Campaign]=_a)

     

     

    Best Regards,
    Zhu

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago


    You cannot measure a measure directly. Either materialize it first, or create separate measures that implement the entire business logic.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from lbendlin.

     

    Hi CarlosPelay,

     

    As lbendlin says, when we try to use a measure as a filter in another measure, a syntax error occurs because the measure cannot be calculated as a fixed constant value.

    To solve this problem, we can use a variable in the second measure to store the value of __CURRENT CAMPAIGN YR.

    Please use the following DAX:

     

    Appeals_Total$_SPONSORSHIP = 
VAR _a=[__CURRENT CAMPAIGN YR]
RETURN
CALCULATE(SUM('Final_RE_Gift Detail'[Appeal Split Amount]),'Final_RE_Gift Detail'[Appeal]="BBFC Sponsorship",'Final_RE_Gift Detail'[Campaign]=_a)

     

     

    Best Regards,
    Zhu

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.