Forum Discussion

nicolasvc's avatar
nicolasvc
Icon for Helper III rankHelper III
4 years ago
Solved

Fill blank values with last not blank value

I have a table with the value of a product for the first month of the year per store, but I would like to fill the blank values ​​with the last value that is not blank, it is possible to do it using the EARLIER function, or it has to be with another method?

 

StoreYearMonthProductValue
120201A1
120202A 
120203A 
............ 
120201B2
120202B 
...............
220201A3
...............

 

StoreYearMonthProductValue
120201A1
120202A1
120203A1
...............
120201B2
120202B2
...............
220201A3
220202A3

 

Need Help
Show and Tell
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, nicolasvc 

     

    You can try creating new calculated columns and measures to fill in blank values.

    1. Calculated column

     

    New value = 
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        'Table',
        [Store] = EARLIER ( 'Table'[Store] )
            && [Product] = EARLIER ( 'Table'[Product] )
    )
)

     

     

       2. Measure

     

    Measure = 
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        ALL('Table'),
        [Store] = MAX ( 'Table'[Store] )
            && [Product] = MAX ( 'Table'[Product] )
    )
)

     

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

      • ERD's avatar
        ERD
        Icon for Community Champion rankCommunity Champion
        4 years ago

        nicolasvc ,

        There are different options, you can try this one:

        Column =
VAR _s = 'Table'[Store]
VAR _p = 'Table'[Product]
VAR _y = 'Table'[Year]
VAR _m =
    MAXX (
        FILTER ( 'Table', 'Table'[Store] = _s && 'Table'[Product] = _p && 'Table'[Value] <> BLANK () ),
        'Table'[Month]
    )
RETURN
    MAXX (
        FILTER ( 'Table', 'Table'[Store] = _s && 'Table'[Product] = _p && 'Table'[Month] = _m ),
        'Table'[Value]
    )

        If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, nicolasvc 

     

    You can try creating new calculated columns and measures to fill in blank values.

    1. Calculated column

     

    New value = 
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        'Table',
        [Store] = EARLIER ( 'Table'[Store] )
            && [Product] = EARLIER ( 'Table'[Product] )
    )
)

     

     

       2. Measure

     

    Measure = 
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        ALL('Table'),
        [Store] = MAX ( 'Table'[Store] )
            && [Product] = MAX ( 'Table'[Product] )
    )
)

     

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi  v-zhangti 

       nicolasvc , the same solution, is there anyway that we can do it in Query Editor ? (in my case non blank value is dynamically generated in a random row based on other column, hence can't use fill up and down) would like to make it through Query editor, not getting a way out. 

      Thanks

      Srinivas