Report Level Measure Using Calculation Items

[littlemojopuppy]

Hi -

I encountered something kind of strange today and I was hoping someone could provide some insight on why this is.

I have a large data model in Analysis Services.  I've defined some calculation groups for things like time intelligence, period to period comparison, etc.  In reports I'm creating report level measures to actually display specific values in visualizations.  Some pages in the reports have slicers to determine which "base" measure to present on a page.

Here's what's odd: when I define my report level measures for specific values, if the measure is defined like this, it ignores using the calculation item definition and just returns the value of the base measure.

Measure to Display Submissions This Week = 
VAR ReportMeasure =
    SWITCH(
        TRUE(),
        SELECTEDVALUE('Display Measures'[DisplayMeasure]) = "Internal Submission", [Internal Submissions],
        SELECTEDVALUE('Display Measures'[DisplayMeasure]) = "Client Submission", [Client Submissions]
    )
RETURN

CALCULATE(
    ReportMeasure,
    'Time Intelligence'[Time Intelligence] = "This Week"
)

However, if I define the measure like this, it does return the appropriate calculation from the calculated item.

Measure to Display Submissions This Week = 
CALCULATE(
    SWITCH(
        TRUE(),
        SELECTEDVALUE('Display Measures'[DisplayMeasure]) = "Internal Submission", [Internal Submissions],
        SELECTEDVALUE('Display Measures'[DisplayMeasure]) = "Client Submission", [Client Submissions]
    ),
    'Time Intelligence'[Time Intelligence] = "This Week"
)

I don't need help with the measure...the second works perfectly.  This is more about understanding why when using a variable the calculated item gets ignored.

Thanks!

[littlemojopuppy]

Hi @v-robertq-msft.  Thank you for your reply and I apologize for the delay in responding.

That makes perfect sense and I should have realized before.  Thank you!

[v-robertq-msft · Accepted solution]

Hi, @littlemojopuppy 

According to my research, it seems that the problem occurs because you quote measures in the variable you defined, which leads to the variable always passes the static value to it regardless of its own calculation. The same principle applies to using nested variables.

Here’s an example that I tested according to your scenario:

Test data:

Measure =

var _sum=SUM('Table'[Rank])

return

CALCULATE(_sum,'Table'[Date end]="Today")

Measure 2 =

var _1=[Measure]

return

CALCULATE(_1,'Table'[Date end]="Today")

Measure 3 =

CALCULATE([Measure],'Table'[Date end]="Today")

Result of the three measures:

As we can see, the measure will be filtered in the calculate() function only when it’s directly placed into the calculate() function instead of be defined in the variable.

Here’s are some related links:

https://community.powerbi.com/t5/Desktop/Use-DAX-variable-withing-measure/m-p/256805

https://blog.enterprisedna.co/using-variables-in-dax-a-detailed-example/

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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