Hi,
I am trying to figure out how can I achieve a fill down of a measure for an inactive raltionship.
To elaborate, the data model is following
Fact[D1] has an active relationship to Date[Date] and Fact[D2] has an inactive relationship to Date[Date].
I calculated running total count for both D1 and D2 and displayed against Date[Date] and succeeded in that. The measures are following
D1 Running Count {Active Relation} :=
VAR _1 =
MAX ( 'Fact'[D1] )
VAR _2 =
CALCULATE ( COUNT ( 'Fact'[D1] ), ALL ( 'Fact'[D1] ), 'Fact'[D1] <= _1 )
RETURN
_2
D2 Running Count {Inactive Relation} :=
VAR _1 =
CALCULATE ( MAX ( 'Fact'[D2] ), USERELATIONSHIP ( 'Date'[Date], 'Fact'[D2] ) )
VAR _2 =
CALCULATE (
CALCULATE ( COUNT ( 'Fact'[D2] ) ),
ALL ( 'Fact' ),
'Fact'[D2] <= _1
)
RETURN
_2
Then, I wanted to create a measure that fills down both the measures. I succeeded with filling down the first measure.
D1 Running Count Measure Fill down {Active Relation} :=
VAR _1 =
MAX ( 'Date'[Date] )
VAR _2 =
CALCULATE (
MAX ( 'Date'[Date] ),
FILTER (
ALL ( 'Date' ),
'Date'[Date] <= _1
&& [D1 Running Count {Active Relation}] <> 0
)
)
VAR _3 =
CALCULATE (
[D1 Running Count {Active Relation}],
ALL ( 'Date' ),
'Date'[Date] = _2
)
RETURN
_3
I tried filling down the second meaure by mimicking the above and did not succeed.
D2 Running Count Measure Fill down {Inactive Relation} :=
VAR _1 =
MAX ( 'Date'[Date] )
VAR _2 =
CALCULATE (
MAX ( 'Date'[Date] ),
FILTER (
ALL ( 'Date' ),
'Date'[Date] <= _1
&& [D2 Running Count {Inactive Relation}] <> 0
)
)
VAR _3 =
CALCULATE (
[D2 Running Count {Inactive Relation}],
ALL ( 'Date' ),
'Date'[Date] = _2
)
RETURN
_3
I am not sure what went wrong. If someone can please take a look and put this measure on the correct direction would be great. The PBIX is attached.
Thank you in advance.
