topic Re: Updating table rows based on earlier row in DAX Commands and Tips

Updating table rows based on earlier row

PaulBoden — Thu, 04 Jul 2024 10:04:35 GMT

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

1	HC	Source	1	03/07/2024	S
1	HC	Source	1	04/07/2024	S
1	L	Adj	1	05/07/2024	S
1	HC	Source	1	06/07/2024	S
1	HC	Source	1	07/07/2024	S
1	L	Source	1	08/07/2024	S
1	HC	Source	1	09/07/2024	S
1	HC	Source	1	10/07/2024	S
1	HC	Source	1	11/07/2024	S
1	HC	Source	1	12/07/2024	S
2	HC	Source	3	03/07/2024	S
2	HC	Source	3	04/07/2024	S
2	HC	Source	3	05/07/2024	S
2	HC	Source	3	06/07/2024	S
2	HC	Source	3	07/07/2024	S
2	L	Source	3	08/07/2024	S
3	HC	Source	3	03/07/2024	S
3	HC	Source	3	04/07/2024	S
3	HC	Source	3	05/07/2024	S
3	HC	Source	3	06/07/2024	S
3	HC	Source	3	07/07/2024	S
3	L	Adj	3	08/07/2024	S
3	HC	Source	3	09/07/2024	S
3	HC	Source	3	10/07/2024	S
3	HC	Source	3	11/07/2024	S
3	HC	Source	3	12/07/2024	S
4	HC	Source	5	03/07/2024	S
4	HC	Source	5	04/07/2024	S
4	L	Adj	5	05/07/2024	S
4	HC	Source	5	05/07/2024	S
4	HC	Source	5	06/07/2024	S
4	HC	Source	5	07/07/2024	S
4	HC	Source	5	08/07/2024	S
4	HC	Source	5	09/07/2024	S
4	HC	Source	5	10/07/2024	S
4	HC	Source	5	11/07/2024	S
4	L	Source	5	12/07/2024	S
5	HC	Source	2	03/07/2024	S
5	M	Adj	3	04/07/2024	S
5	HC	Source	2	04/07/2024	S
5	HC	Source	2	05/07/2024	S
5	HC	Source	2	06/07/2024	S
5	HC	Source	2	07/07/2024	S
5	HC	Source	2	08/07/2024	S
5	M	Source	3	09/07/2024	S
5	HC	Source	3	10/07/2024	S
5	HC	Source	3	11/07/2024	S
5	HC	Source	3	12/07/2024	S
5	HC	Source	3	13/07/2024	S

Re: Updating table rows based on earlier row

Anonymous — Fri, 05 Jul 2024 06:05:09 GMT

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?

Best Regards

Re: Updating table rows based on earlier row

PaulBoden — Fri, 05 Jul 2024 08:41:42 GMT

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

ID	Case	Flag	Level	Date	Output
1	HC	Source	1	03/07/2024	S
1	HC	Source	1	04/07/2024	S

Re: Updating table rows based on earlier row

Anonymous — Mon, 08 Jul 2024 07:17:15 GMT

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

Re: Updating table rows based on earlier row

PaulBoden — Mon, 08 Jul 2024 16:37:01 GMT

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

Re: Updating table rows based on earlier row

Anonymous — Wed, 10 Jul 2024 08:47:47 GMT

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" )

Best Regards