Last non-blank measure fill down for inactive relationship

[smpa01]

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.

[smpa01]

Hello @v-kelly-msft,

I was wondering if you had a chance to look at this ?

Thanks

[v-kelly-msft]

Hi @smpa01 ,

I'm a little confused ,what is your expected output?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[smpa01]

Hi @v-kelly-msft ,

Many thanks for looking into this.

My desired outcome are the values in column - D2 Running Count Measure Fill down {Inactive Relation}

Date	D1 Running Count {Active Relation}	D2 Running Count {Inactive Relation}	D1 Running Count Measure Fill down {Active Relation}	D2 Running Count Measure Fill down {Inactive Relation}
8/1/2020 0:00	1		1
8/2/2020 0:00			1
8/3/2020 0:00			1
8/4/2020 0:00			1
8/5/2020 0:00	2		2
8/6/2020 0:00			2
8/7/2020 0:00			2
8/8/2020 0:00			2
8/9/2020 0:00			2
8/10/2020 0:00			2
8/11/2020 0:00			2
8/12/2020 0:00			2
8/13/2020 0:00			2
8/14/2020 0:00			2
8/15/2020 0:00			2
8/16/2020 0:00			2
8/17/2020 0:00	3		3
8/18/2020 0:00			3
8/19/2020 0:00			3
8/20/2020 0:00			3
8/21/2020 0:00			3
8/22/2020 0:00			3
8/23/2020 0:00			3
8/24/2020 0:00			3
8/25/2020 0:00			3
8/26/2020 0:00			3
8/27/2020 0:00			3
8/28/2020 0:00			3
8/29/2020 0:00			3
8/30/2020 0:00	4		4
8/31/2020 0:00			4
9/1/2020 0:00	5		5
9/2/2020 0:00			5
9/3/2020 0:00			5
9/4/2020 0:00			5
9/5/2020 0:00			5
9/6/2020 0:00			5
9/7/2020 0:00	6	1	6	1
9/8/2020 0:00			6	1
9/9/2020 0:00			6	1
9/10/2020 0:00			6	1
9/11/2020 0:00			6	1
9/12/2020 0:00			6	1
9/13/2020 0:00			6	1
9/14/2020 0:00			6	1
9/15/2020 0:00	7		7	1
9/16/2020 0:00			7	1
9/17/2020 0:00			7	1
9/18/2020 0:00			7	1
9/19/2020 0:00	8		8	1
9/20/2020 0:00			8	1
9/21/2020 0:00			8	1
9/22/2020 0:00			8	1
9/23/2020 0:00			8	1
9/24/2020 0:00			8	1
9/25/2020 0:00			8	1
9/26/2020 0:00			8	1
9/27/2020 0:00			8	1
9/28/2020 0:00			8	1
9/29/2020 0:00			8	1
9/30/2020 0:00			8	1
10/1/2020 0:00	9	2	9	2
10/2/2020 0:00			9	2
10/3/2020 0:00			9	2
10/4/2020 0:00			9	2
10/5/2020 0:00			9	2
10/6/2020 0:00			9	2
10/7/2020 0:00		3	9	3
10/8/2020 0:00			9	3
10/9/2020 0:00			9	3
10/10/2020 0:00			9	3
10/11/2020 0:00			9	3
10/12/2020 0:00			9	3
10/13/2020 0:00			9	3
10/14/2020 0:00			9	3
10/15/2020 0:00		4	9	4
10/16/2020 0:00			9	4
10/17/2020 0:00			9	4
10/18/2020 0:00			9	4
10/19/2020 0:00			9	4
10/20/2020 0:00			9	4
10/21/2020 0:00			9	4
10/22/2020 0:00		5	9	5
10/23/2020 0:00			9	5
10/24/2020 0:00			9	5
10/25/2020 0:00		6	9	6
10/26/2020 0:00			9	6
10/27/2020 0:00			9	6
10/28/2020 0:00			9	6
10/29/2020 0:00			9	6
10/30/2020 0:00			9	6
10/31/2020 0:00			9	6
11/1/2020 0:00			9	6
11/2/2020 0:00			9	6
11/3/2020 0:00			9	6
11/4/2020 0:00			9	6
11/5/2020 0:00			9	6
11/6/2020 0:00			9	6
11/7/2020 0:00			9	6
11/8/2020 0:00			9	6
11/9/2020 0:00			9	6
11/10/2020 0:00			9	6
11/11/2020 0:00			9	6
11/12/2020 0:00			9	6
11/13/2020 0:00			9	6
11/14/2020 0:00			9	6
11/15/2020 0:00			9	6
11/16/2020 0:00			9	6
11/17/2020 0:00			9	6
11/18/2020 0:00			9	6
11/19/2020 0:00			9	6
11/20/2020 0:00			9	6
11/21/2020 0:00			9	6
11/22/2020 0:00			9	6
11/23/2020 0:00			9	6
11/24/2020 0:00			9	6
11/25/2020 0:00			9	6
11/26/2020 0:00			9	6
11/27/2020 0:00			9	6
11/28/2020 0:00			9	6
11/29/2020 0:00			9	6
11/30/2020 0:00			9	6
12/1/2020 0:00			9	6
12/2/2020 0:00			9	6
12/3/2020 0:00			9	6
12/4/2020 0:00			9	6
12/5/2020 0:00			9	6
12/6/2020 0:00			9	6
12/7/2020 0:00			9	6
12/8/2020 0:00			9	6
12/9/2020 0:00			9	6
12/10/2020 0:00			9	6
12/11/2020 0:00			9	6
12/12/2020 0:00			9	6
12/13/2020 0:00			9	6
12/14/2020 0:00			9	6
12/15/2020 0:00			9	6
12/16/2020 0:00			9	6
12/17/2020 0:00			9	6
12/18/2020 0:00			9	6
12/19/2020 0:00			9	6
12/20/2020 0:00			9	6
12/21/2020 0:00			9	6
12/22/2020 0:00			9	6
12/23/2020 0:00			9	6
12/24/2020 0:00			9	6
12/25/2020 0:00			9	6
12/26/2020 0:00			9	6
12/27/2020 0:00			9	6
12/28/2020 0:00			9	6
12/29/2020 0:00			9	6
12/30/2020 0:00			9	6
12/31/2020 0:00			9	6