Sorting And Comparing Row in Power BI

[NSBS]

Hi Power BI Community,

Need help in creating DAX code or power query code to create additional column (Run # and Final Status) in below File Run Dataset Table

Initial column detail as per below:

1) Filename : a file to run in a process
2) Run Id : an ID for a file process run
3) Attempt: : # of process run attempt for each Run Id
4) Loaded Date /Time : Date and Time of a file loaded into each process run
5) Status : Status of each attempt of a process run

Additional column needed

1) Run # : The process run # for a filename and Run Id sorted by its Loaded Date / Time
2) Final Status : The status for each Run Id
(ie: if the last attempt for each run Id is Completed, then all row in each Run Id equal to Completed, else Failed)

Table Name: File Run Dataset

Filename	Run Id	Attempt	Loaded Date / Time	Status	Run #	Final Status
D_221210_ARG_8	11111	1	12/13/2022 0:00 AM	Failed	1	Failed
D_221210_ARG_8	11111	2	12/13/2022 0:00 AM	Failed	1	Failed
D_221210_ARG_8	11111	3	12/13/2022 0:00 AM	Failed	1	Failed
D_221210_ARG_8	11112	1	12/13/2022 10:00 AM	Failed	2	Failed
D_221210_ARG_8	11112	2	12/13/2022 10:00 AM	Failed	2	Failed
D_221210_ARG_8	11112	3	12/13/2022 10:00 AM	Failed	2	Failed
D_221210_ARG_8	11113	1	12/15/2022 2:00 AM	Failed	3	Failed
D_221210_ARG_8	11113	2	12/15/2022 2:00 AM	Failed	3	Failed
D_221210_ARG_8	11113	3	12/15/2022 2:00 AM	Failed	3	Failed
D_221210_ARG_8	11114	2	12/15/2022 11:00 AM	Completed	4	Completed
D_221210_ARG_8	11114	1	12/15/2022 11:00 AM	Failed	4	Completed

[FreemanZ]

hi @NSBS 

try to add the expected columns like this:

Rank #2 = 
RANKX(
    ALL(TableName[Loaded Date / Time]),
    TableName[Loaded Date / Time],
    ,ASC
)
Final Status2 = 
VAR _runid = [Run Id]
VAR _table = 
FILTER(
    TableName,
    TableName[Run Id] = _runid
    )
RETURN
MINX(
     _table,
    TableName[Status]
)

it shall work like this:

[amitchandak · Accepted solution]

@NSBS , New columns

Run = rankx(filter(Table, [File Name] = earlier([Filename]) ), [Loaded Date/Time],,asc,dense)

Final Status = minx(filter(Table, [Run] = earlier([Run]) ) ,[Status] )