Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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