cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TGS84 Visitor
Visitor

Dax Measure Fill Down Value

I have two measures which calculate a value for a year. See following table.

My goal is to fill the gaps for measure B with the last available value. The result should look like:

 

Which DAX Construct can be used to solve my problem?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Dax Measure Fill Down Value

@TGS84,

 

You may add an additional measure.

Measure =
VAR y =
    SELECTEDVALUE ( Table1[Year] )
RETURN
    CALCULATE (
        [Measure B],
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( Table1[Year] ),
                Table1[Year] <= y
                    && NOT ( ISBLANK ( [Measure B] ) )
            ),
            Table1[Year], DESC
        )
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: Dax Measure Fill Down Value

Hi @TGS84

 

What is the code for your [Measure B]?

One way would be to recreate what happens in the matrix within the measure, i.e. by building a dynamic table with the years in the first column and measure B in the second column and then look for the LASTNONBLANK. But depending on the code for your measure it might be simpler than that. That is why i am asking you to show it.        

Highlighted
Community Support Team
Community Support Team

Re: Dax Measure Fill Down Value

@TGS84,

 

You may add an additional measure.

Measure =
VAR y =
    SELECTEDVALUE ( Table1[Year] )
RETURN
    CALCULATE (
        [Measure B],
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( Table1[Year] ),
                Table1[Year] <= y
                    && NOT ( ISBLANK ( [Measure B] ) )
            ),
            Table1[Year], DESC
        )
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

JPGV Frequent Visitor
Frequent Visitor

Re: Dax Measure Fill Down Value

@v-chuncz-msft This just solved a big problem for me, thanks!

 

However, the measure is not giving me a total. I think I understand why it does not, but I don't know how to solve it.

 

I modified the formula in this way:

 
Measure =
VAR y =
SELECTEDVALUE ( 'dim DimDate'[DATE] )
RETURN
CALCULATE (
[Backlog Previous Day],
TOPN (
1,
FILTER (
ALLSELECTED ( 'dim DimDate'[DATE] ),
'dim DimDate'[DATE] <= y
&& NOT ( ISBLANK ( [Backlog Previous Day] ) )
),
'dim DimDate'[DATE], DESC
)
)

 

It is giving me exactly what I needed on a row level, but won't return a total. Is there a way to modify the measure?

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors