Forum Discussion

gsksarepta's avatar
gsksarepta
Helper II
2 years ago
Solved

Help on Missing data fill techniques

Hi, I have table like this in PowerBI I need to fill the missing values in 4th column based on the respective values step3afin column.. for example. Here is the before after table I am hoping to get from this tranformation.. Copy down and copy up is not doing fill correctly because the non-empty value position varies. any help is appreciated. I am okay either new column or filling the exisiting column

 

  Table Before     Table after transformation
step1runstep2tffstep3afinstep4aex   step1runstep2tffstep3afinstep4aexnewresult
ab11tff1ds1    ab11tff1ds1 aeds2
ab11tff2ds1    ab11tff2ds1 aeds2
ab11tff3ds1aeds2   ab11tff3ds1aeds2aeds2
ab11tff4ds1    ab11tff4ds1 aeds2
ab51tff5ds5    ab51tff5ds5 aeds6
ab51tff6ds5aeds6   ab51tff6ds5aeds6aeds6
ab51tff7ds5    ab51tff7ds5 aeds6
ab51tff8ds5    ab51tff8ds5 aeds6
ab91tff9ds9aeds10   ab91tff9ds9aeds10aeds10
ab91tff10ds9    ab91tff10ds9 aeds10
ab91tff11ds9    ab91tff11ds9 aeds10
ab91tff12ds9    ab91tff12ds9 aeds10
ab131tff13ds13    ab131tff13ds13 aeds14
ab131tff14ds13    ab131tff14ds13 aeds14
ab131tff15ds13    ab131tff15ds13 aeds14
ab131tff16ds13aeds14   ab131tff16ds13aeds14aeds14
ab141tff17ds14    ab141tff17ds14  
ab141tff18ds14    ab141tff18ds14  
ab141tff19ds14    ab141tff19ds14  
ab141tff20ds14    ab141tff20ds14  
ab151tff21ds15    ab151tff21ds15 aeds16
ab151tff22ds15    ab151tff22ds15 aeds16
ab151tff23ds15aeds16   ab151tff23ds15aeds16aeds16
ab151tff24ds15    ab151tff24ds15 aeds16
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi gsksarepta ,

    Please try to create a new column with below dax formula:

    newresult =
VAR _step3 = [step3afin]
VAR _a =
    CALCULATE (
        MAX ( 'Table'[step4aex] ),
        FILTER ( 'Table', [step3afin] = _step3 )
    )
RETURN
    _a

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi gsksarepta ,

    Please try to create a new column with below dax formula:

    newresult =
VAR _step3 = [step3afin]
VAR _a =
    CALCULATE (
        MAX ( 'Table'[step4aex] ),
        FILTER ( 'Table', [step3afin] = _step3 )
    )
RETURN
    _a

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    gsksarepta , Try using this method

     

    newresult =
    VAR CurrentRow = YourTable[step4aex]
    VAR NonEmptyValues =
    FILTER(
    YourTable,
    YourTable[step3afin] = CurrentRow
    && NOT(ISBLANK(YourTable[step4aex]))
    )
    VAR Result =
    IF(
    COUNTROWS(NonEmptyValues) > 0,
    CALCULATE(
    MAXX(NonEmptyValues, NonEmptyValues[step4aex])
    ),
    BLANK()
    )
    RETURN Result

     

    Please accept as solution and give kudos if it helps

    • gsksarepta's avatar
      gsksarepta
      Helper II
      2 years ago

      Hi bhanu_gautam Thanks for your time, I tried it on my PowerBI Table view added it as column measure but got this error, do you see any issues with the script or the way I did?  

       

       

      • bhanu_gautam's avatar
        bhanu_gautam
        Super User
        2 years ago

        Try this

        newresult =
        VAR CurrentRow = YourTable[step4aex]
        VAR NonEmptyValues =
        VALUES (
        FILTER (
        YourTable,
        YourTable[step3afin] = CurrentRow
        && NOT(ISBLANK(YourTable[step4aex]))
        )
        )
        VAR Result =
        IF (
        COUNTROWS(NonEmptyValues) > 0,
        MAXX(NonEmptyValues, [step4aex]),
        BLANK()
        )
        RETURN
        Result

    • gsksarepta's avatar
      gsksarepta
      Helper II
      2 years ago

      Hi Amit amitchandak , I didn't understand your response, do you mind adding some more details on what to do ?  Meanwhile, I will go through the links for ideas. Thanks for your time.