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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
NSBS
Helper I
Helper I

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
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

 

 

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

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:

FreemanZ_0-1672197373447.png

amitchandak
Super User
Super User

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

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.