How can I make a dynamic Measure behave like a hard coded number ?

[KBL1974]

Hi All,
Im trying to solve a DAX dilemma. I relates to the way a measure is handled by another measure.
I'll try to explain clearly.

------------------------------------------------------------------------------------------------------------------------------------------

[Measure1] calculates the maximum days in the current week where we have data.

Measure1:=CALCULATE (
MAX ( 'Activations'[DayOrder] ),
FILTER ( 'Date', 'Date'[Relative Week] = "Current Week" ),
FILTER ( 'Activations', [Units] > 0 )
)

The result is 4
------------------------------------------------------------------------------------------------------------------------------------------

[Measure2] now calculates the equivalent days from the previous week, using the calculation result from [Measure1] 4.

Measure2:=CALCULATE (
[Units],
FILTER ( 'Date', 'Date'[Relative Week] = "Current Week -1" ),
FILTER (
'Activations',
[DayOrder]
<= CALCULATE (
MAX ( 'Activations'[DayOrder] ),
FILTER ( 'Date', 'Date'[Relative Week] = "Current Week" ),
FILTER ( 'Activations', [Units] > 0 )
)
)
)

The result is 123,456.

------------------------------------------------------------------------------------------------------------------------------------------
Issue:

When creating a pivot table using [Date] on rows and [Measure2] on columns we only get a sum total value.

We cannot drill down any further

I need to be able to have the [Date] granularity

------------------------------------------------------------------------------------------------------------------------------------------

[Measure3] is the same as [Measure2] except it uses the hard coded value 4 instead of the calculation result from [Measure1] ) also 4).

Measure3:=CALCULATE (
[Units],
FILTER ( 'Date', 'Date'[Relative Week] = "Current Week -1" ),
FILTER (
'Activations',
[DayOrder]
<= 4
)
)

The result is also 123,456 but this time the [Date] granularity is available.


------------------------------------------------------------------------------------------------------------------------------------------

So the question is :    How can I make the dynamic [Measure1], (which calculates to 4), act like a hardcoded number 4 ? 

Thank you in advance.

[v-shex-msft]

HI @KBL1974,

For your scenario, it seems like the filter not correct works on your nested measures, can please share the sample pbix file to test?

Did you try to use var function to store the measure result, the use it as the condition in filter?

Measure2 :=
VAR temp =
    CALCULATE (
        MAX ( 'Activations'[DayOrder] ),
        FILTER ( 'Date', 'Date'[Relative Week] = "Current Week" ),
        FILTER ( 'Activations', [Units] > 0 )
    )
RETURN
    CALCULATE (
        [Units],
        FILTER ( 'Date', 'Date'[Relative Week] = "Current Week -1" ),
        FILTER ( 'Activations', [DayOrder] <= temp )
    )

BTW, I think [Units] is also a measure, right?

Regards,

Xiaoxin Sheng