Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
smpa01
Super User
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

Capture.PNG

 

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.

Did I answer your question? Mark my post as a solution!
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
smpa01
Super User
Super User

Hello @v-kelly-msft,

 

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

 

Thanks

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
v-kelly-msft
Community Support
Community Support

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!

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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.