cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Super User

## Last non-blank measure fill down for inactive relationship

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.

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
3 REPLIES 3
Super User

Hello @v-kelly-msft,

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

Thanks

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Community Support

Hi @smpa01 ,

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

Best Regards,
Kelly

Super User

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
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors