Forum Discussion

PaulBoden's avatar
PaulBoden
Helper I
2 years ago
Solved

Updating table rows based on earlier row

Hi all,

 

I have a solution that needs to integrate manual updates during downtimes before eventually reconciling the data once the correct information becomes available at source.

 

The solution needs to:

  • Find instances where a flag has indicated a manual adjustment has been made to a user entry
  • Identify any entries associated with this user ID between the date on which the manual adjustment was made and the date on which the source data has been updated to reflect the manual adjustment (if applicable)
  • Apply a specific set of adjustments to the identified rows, dependent on the nature of the adjustment.

 

Considering the attached table, I’ve tried to outline the logic I’m trying to implement below:

For the purposes of this explanation, I’ll refer to the manual adjustment as row X.

 

IF Flag = “Adj” (

                IF Case = “L” (

                                For any Date >=  Than X.Date WHERE ID = X.ID And Case != X.Case

                                                Output = “H”

                )

                IF Case = “M” (

                                For any Date >=  Than X.Date WHERE ID = X.ID And Case != X.Case

                                                Level = X.Level

                )

)

 

Any advice on how to optimally achieve this within PowerBI would be greatly appreciated.

 

IDCaseFlagLevelDateOutput

1HCSource103/07/2024S
1HCSource104/07/2024S
1LAdj105/07/2024S
1HCSource106/07/2024S
1HCSource107/07/2024S
1LSource108/07/2024S
1HCSource109/07/2024S
1HCSource110/07/2024S
1HCSource111/07/2024S
1HCSource112/07/2024S
2HCSource303/07/2024S
2HCSource304/07/2024S
2HCSource305/07/2024S
2HCSource306/07/2024S
2HCSource307/07/2024S
2LSource308/07/2024S
3HCSource303/07/2024S
3HCSource304/07/2024S
3HCSource305/07/2024S
3HCSource306/07/2024S
3HCSource307/07/2024S
3LAdj308/07/2024S
3HCSource309/07/2024S
3HCSource310/07/2024S
3HCSource311/07/2024S
3HCSource312/07/2024S
4HCSource503/07/2024S
4HCSource504/07/2024S
4LAdj505/07/2024S
4HCSource505/07/2024S
4HCSource506/07/2024S
4HCSource507/07/2024S
4HCSource508/07/2024S
4HCSource509/07/2024S
4HCSource510/07/2024S
4HCSource511/07/2024S
4LSource512/07/2024S
5HCSource203/07/2024S
5MAdj304/07/2024S
5HCSource204/07/2024S
5HCSource205/07/2024S
5HCSource206/07/2024S
5HCSource207/07/2024S
5HCSource208/07/2024S
5MSource309/07/2024S
5HCSource310/07/2024S
5HCSource311/07/2024S
5HCSource312/07/2024S
5HCSource313/07/2024S
Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    Hello PaulBoden,

     

    Can you please try the following:

     

    1. Identify Adjustment Rows

    IsAdjustment = 
IF (
    Data[Flag] = "Adj",
    TRUE(),
    FALSE()
)

    2. Apply the logic for updating the Output column

    AdjustedOutput = 
VAR CurrentID = Data[ID]
VAR CurrentDate = Data[Date]
VAR CurrentCase = Data[Case]
VAR AdjustmentDate = 
    CALCULATE (
        MIN(Data[Date]),
        FILTER (
            Data,
            Data[ID] = CurrentID && 
            Data[Flag] = "Adj" && 
            Data[Date] <= CurrentDate
        )
    )
VAR AdjustmentCase = 
    CALCULATE (
        FIRSTNONBLANK(Data[Case], 1),
        FILTER (
            Data,
            Data[ID] = CurrentID && 
            Data[Flag] = "Adj" && 
            Data[Date] = AdjustmentDate
        )
    )
VAR AdjustmentLevel = 
    CALCULATE (
        FIRSTNONBLANK(Data[Level], 1),
        FILTER (
            Data,
            Data[ID] = CurrentID && 
            Data[Flag] = "Adj" && 
            Data[Date] = AdjustmentDate
        )
    )
RETURN 
    IF (
        Data[Flag] <> "Adj" && Data[Date] >= AdjustmentDate,
        IF (
            AdjustmentCase = "L",
            "H",
            IF (
                AdjustmentCase = "M",
                AdjustmentLevel,
                Data[Output]
            )
        ),
        Data[Output]
    )

    Let me know if you might require any further assistance.

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    Hello PaulBoden,

     

    Can you please try the following:

     

    1. Identify Adjustment Rows

    IsAdjustment = 
IF (
    Data[Flag] = "Adj",
    TRUE(),
    FALSE()
)

    2. Apply the logic for updating the Output column

    AdjustedOutput = 
VAR CurrentID = Data[ID]
VAR CurrentDate = Data[Date]
VAR CurrentCase = Data[Case]
VAR AdjustmentDate = 
    CALCULATE (
        MIN(Data[Date]),
        FILTER (
            Data,
            Data[ID] = CurrentID && 
            Data[Flag] = "Adj" && 
            Data[Date] <= CurrentDate
        )
    )
VAR AdjustmentCase = 
    CALCULATE (
        FIRSTNONBLANK(Data[Case], 1),
        FILTER (
            Data,
            Data[ID] = CurrentID && 
            Data[Flag] = "Adj" && 
            Data[Date] = AdjustmentDate
        )
    )
VAR AdjustmentLevel = 
    CALCULATE (
        FIRSTNONBLANK(Data[Level], 1),
        FILTER (
            Data,
            Data[ID] = CurrentID && 
            Data[Flag] = "Adj" && 
            Data[Date] = AdjustmentDate
        )
    )
RETURN 
    IF (
        Data[Flag] <> "Adj" && Data[Date] >= AdjustmentDate,
        IF (
            AdjustmentCase = "L",
            "H",
            IF (
                AdjustmentCase = "M",
                AdjustmentLevel,
                Data[Output]
            )
        ),
        Data[Output]
    )

    Let me know if you might require any further assistance.