Forum Discussion

NSBS's avatar
NSBS
Helper I
3 years ago
Solved

Sorting And Comparing Row in Power BI

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

FilenameRun IdAttemptLoaded Date / TimeStatusRun #Final Status
D_221210_ARG_811111112/13/2022 0:00 AMFailed1Failed
D_221210_ARG_811111212/13/2022 0:00 AMFailed1Failed
D_221210_ARG_811111312/13/2022 0:00 AMFailed1Failed
D_221210_ARG_811112112/13/2022 10:00 AMFailed2Failed
D_221210_ARG_811112212/13/2022 10:00 AMFailed2Failed
D_221210_ARG_811112312/13/2022 10:00 AMFailed2Failed
D_221210_ARG_811113112/15/2022 2:00 AMFailed3Failed
D_221210_ARG_811113212/15/2022 2:00 AMFailed3Failed
D_221210_ARG_811113312/15/2022 2:00 AMFailed3Failed
D_221210_ARG_811114212/15/2022 11:00 AMCompleted4Completed
D_221210_ARG_811114112/15/2022 11:00 AMFailed4Completed
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

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

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

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

     

     

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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: