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 ...
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.

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