Forum Discussion

pabb's avatar
pabb
Frequent Visitor
3 years ago
Solved

Fill in blank rows on calculated table with previous value

Hey,

 

I want to create a calculated column on a calculated table and fill in blank rows with previous values. I cannot do it with measures.

 

I have the following data:

 

the rows I want to fill in are in column [tc al dof], however, the data is pretty inconsistent.

 

Any ideas?

 

Regards,

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi pabb ,

     

    Please create  a new calculated column:

    tc al dof 2 = 
VAR _index = 'Table'[Numero Semana]
VAR _max_index = CALCULATE(MAX('Table'[Numero Semana]),FILTER(ALL('Table'),'Table'[Numero Semana]<_index && NOT ISBLANK('Table'[tc al dof])))
VAR _result =IF(ISBLANK('Table'[tc al dof]),CALCULATE(MAX('Table'[tc al dof]),FILTER(ALL('Table'),'Table'[Numero Semana]=_max_index)),'Table'[tc al dof])
RETURN
_result

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi pabb ,

     

    Please create  a new calculated column:

    tc al dof 2 = 
VAR _index = 'Table'[Numero Semana]
VAR _max_index = CALCULATE(MAX('Table'[Numero Semana]),FILTER(ALL('Table'),'Table'[Numero Semana]<_index && NOT ISBLANK('Table'[tc al dof])))
VAR _result =IF(ISBLANK('Table'[tc al dof]),CALCULATE(MAX('Table'[tc al dof]),FILTER(ALL('Table'),'Table'[Numero Semana]=_max_index)),'Table'[tc al dof])
RETURN
_result

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • pabb's avatar
      pabb
      Frequent Visitor
      3 years ago

      This worked, thanks a lot Anonymous 

  • Mkarwa-123's avatar
    Mkarwa-123
    Icon for Resolver II rankResolver II
    3 years ago

    pabb try to use earlier() dax function.

    Did I answer your question? Mark my post as a solution! If my post helped you, why not give it a thumbs-up?

  • sergej_og's avatar
    sergej_og
    Icon for Super User rankSuper User
    3 years ago

    Hi pabb ,
    is column [tc al dof] accesable in PQ View or is your table completely calculated table?
    In PQ you can easily fill blank cells up or down.

     

  • HKInd's avatar
    HKInd
    Icon for Helper I rankHelper I
    3 years ago
    Fill =
    var curr = 'Table'[tc al dof]
    var prerow =
    CALCULATE(MAX('Table'[tc al dof]),
    FILTER(
        ALL('Table'),
        'Table'[tc al dof]<> BLANK() && 'Table'[tc al dof]<>curr
    )
    )
    RETURN
    IF(ISBLANK(curr),
    prerow,curr)