Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
PaulBoden
Helper I
Helper I

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
1 ACCEPTED SOLUTION

Hi @PaulBoden ,

You can create a calculated column as below to get it, please find the details in the attachment.

Output = 
VAR _id = 'Table'[ID]
VAR _date = 'Table'[Date]
VAR _case = 'Table'[Case]
VAR _level = 'Table'[Level]
VAR _ladjdate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[ID] = _id
                && 'Table'[Flag] = "Adj"
                && 'Table'[Case] = "L"
        )
    )
VAR _madjdate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[ID] = _id
                && 'Table'[Flag] = "Adj"
                && 'Table'[Case] = "M"
        )
    )
RETURN
    IF (
        (
            'Table'[ID] = _id
                && NOT ( ISBLANK ( _ladjdate ) )
                    && 'Table'[Date] >= _ladjdate
                    && 'Table'[Case] <> "L"
        )
            || (
                'Table'[ID] = _id
                    && NOT ( ISBLANK ( _madjdate ) )
                        && 'Table'[Date] >= _madjdate
                        && 'Table'[Case] <> "M"
                        && 'Table'[Level] = _level
            ),
        "H"
    )

vyiruanmsft_0-1720601225199.png

Best Regards

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

View solution in original post

5 REPLIES 5
v-yiruan-msft
Community Support
Community Support

Hi @PaulBoden ,

I'm not clear about your requirement. Do you want to get the field [Output]? Could you please explain the judgement logic base on your provided sample data?

Explain the judgement logic for it base on the sample dataExplain the judgement logic for it base on the sample data

Expected result is Output field?Expected result is Output field?

Best Regards

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

Apologies for being unclear - The table should be formatted as below.

 

IDCase  Flag Level Date Output
1HCSource103/07/2024S
1HCSource104/07/2024S

Hi @PaulBoden ,

Could you please explain the judgement logic of getting the field [Output] base on the shared sample data and below formula? For example: if xx="Adj" && xxx = xxx then xx 

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

                )

)

 

Best Regards

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

I'm sorry, I'm not sure I understand the question. 

 

If a record has "[Flag] = "Adj" and [Case] = "L", I want to set [Output] ="H" for all other records with the same [ID] where [Date] > than the date value contained in this record and where [Case] != "L"".

 

The second part of the logic ([Case] != "L") is because eventually the golden source will update and a record will be ingested containing x.[ID], x.[Case] and [Flag] = Source.

 

Hopefully this helps clarify?

 

Someone else suggested the below to me:

 

let
    Source = Table.Sort(your_table, {"ID", "Date"}),
    rec = (x) => [L = {"Output", each "H"}, M = {"Level", each x{0}[Level]}],
    fx = (tbl) => Table.TransformColumns(tbl, Record.FieldOrDefault(rec(tbl), tbl{0}[Case], {})),
    group = Table.Group(
        Source, 
        {"ID", "Case"},
        {"x", fx},
        GroupKind.Local, 
        (s, c) => Number.From(s[ID] <> c[ID] or List.Contains({"L", "M"}, c[Case]))
    ),
    z = Table.Combine(group[x])
in
    z

This seems to achive close to the outcome I'm looking for, although I suspect for performance optimisation, it would be better to execute this as a step within the existing query, rather than a standalone. Additionally, the outputs seem slightly different from what I'm expecting. I wonder if it's because the flag logic isn't considered and the rules are applied to all records regardless.

 

If you have any thoughts on how to update to achieve the correct output, I'd be greatly appreciative.

 

Thanks

Hi @PaulBoden ,

You can create a calculated column as below to get it, please find the details in the attachment.

Output = 
VAR _id = 'Table'[ID]
VAR _date = 'Table'[Date]
VAR _case = 'Table'[Case]
VAR _level = 'Table'[Level]
VAR _ladjdate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[ID] = _id
                && 'Table'[Flag] = "Adj"
                && 'Table'[Case] = "L"
        )
    )
VAR _madjdate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[ID] = _id
                && 'Table'[Flag] = "Adj"
                && 'Table'[Case] = "M"
        )
    )
RETURN
    IF (
        (
            'Table'[ID] = _id
                && NOT ( ISBLANK ( _ladjdate ) )
                    && 'Table'[Date] >= _ladjdate
                    && 'Table'[Case] <> "L"
        )
            || (
                'Table'[ID] = _id
                    && NOT ( ISBLANK ( _madjdate ) )
                        && 'Table'[Date] >= _madjdate
                        && 'Table'[Case] <> "M"
                        && 'Table'[Level] = _level
            ),
        "H"
    )

vyiruanmsft_0-1720601225199.png

Best Regards

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors