Pipelie status based on Latest pipeline Runtime in PowerBI

[Anonymous]

Hi, I am working on a Monitoring Dashbaord. I am having SourceName, RunDate , Pipeline Status Columns in my table visual.  And I have 2 stages in my datset landing and  staging. So i am trying to acheive two graphs.

1)Either the particular source is successfully loaded or not in landing layer.

2)Either the particular source is successfully loaded or not in Staging layer.

You can see in the above image verwijk source is successful and failed on 7/11/2024. But the strat time is differant . the latest start time for that particular source is 02.49 and at 02.49 it is successful. And at 01.59 it is failure. so I want to get the latest StartTime pipeline status result in my report. How could I achevie it. As I am beginner to PowerBi. Any ideas or help would be really helpful for me.

Regards,

Srsrm

[Anonymous]

Thanks again for the reply. I used some calculated column and measures to acheive it. But  not getting the wanted result.

My datset looks like below Here you can see source E on 12/11/2024 is  failed at 17.07 but successfil at 17.10 So I want to see only success state for source E. Because 17.10 is the latest start time.   And Source N on 11/11 failed at 09.30 but successful at 09.31. 01 so I want to see the status at 09.31.01. along with other sources data. could you please help me.

Thanks in advance.

[lbendlin]

Would you mind posting the sample data in a usable format, not as a screenshot?

[Anonymous]

Yes, Sure.

SourceName	RunDate	PipelineStatus	StageName	StartTime
A	12/11/2024	Success	Staging Delta	21:29:58
D	12/11/2024	Success	Staging Delta	17:07:13
E	12/11/2024	Success	Staging Delta	17:10:55
E	12/11/2024	Failed	Staging Delta	17:07:21
G	12/11/2024	Success	Staging Delta	17:07:16
H	12/11/2024	Failed	Staging Delta	17:07:29
H	12/11/2024	Success	Staging Delta	17:07:16
I	12/11/2024	Success	Staging Delta	17:07:26
K	11/11/2024	Success	Staging Delta	9:30:14
L	11/11/2024	Success	Staging Delta	9:30:08
M	11/11/2024	Success	Staging Delta	9:30:33
N	11/11/2024	Failed	Staging Delta	9:30:20
N	11/11/2024	Success	Staging Delta	9:31:01

[Anonymous]

I was not able to attach the file. So I copied the data directly.

Thank you.

[lbendlin]

Here is the Power Query version

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldE9C4MwEAbgv1IyC95dtDa3FewX/Vg6BodgRQTpov3/jclWYkm2XODhPe7VWuxFJpByxJyACjs8P23bTdPymk0/vPtN3Y2zsTMhk+JyJ5pMizreYcVQMUrnDkkOgcsy7I5mGLvXahyhY6fkNbfOnVPjVJhFxl1SHXl3XT4wyimWwFg4dktk4Du/JzLpK3/8stVbOkUQVn/DkMEW3nwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SourceName = _t, RunDate = _t, PipelineStatus = _t, StageName = _t, StartTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RunDate", type date}, {"StartTime", type time}},"es-MX"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SourceName", "RunDate"}, {{"Latest Time", each List.Max([StartTime]), type nullable time}, {"Rows", each _, type table [SourceName=nullable text, RunDate=nullable date, PipelineStatus=nullable text, StageName=nullable text, StartTime=nullable time]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.SelectRows([Rows],(k)=> k[StartTime]=[Latest Time])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"PipelineStatus", "StageName"}, {"Latest PipelineStatus", "StageName"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Rows"})
in
    #"Removed Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[Anonymous]

Hi , To acheive this

1) First I created Calculated column with 

LateststartTime = CALCULATE(MAX('repo Monitoring'[StartTime]),ALLEXCEPT('repo Monitoring','reporting Monitoring'[RunDate],'reporting Monitoring'[SourceName])) then 

2)

Latest StartTime Indicator = if('repo Dashboard'[LateststartTime]='repo Monitoring'[StartTime],1,0) . after that I kept the indicator in Filter and selected '1'

Then it's working.

Thanks alot for your support and help.

[Anonymous]

Thanks for the reply. But the Rundate column is having all dates from jan 2024 to today. If I do like above I am only getting the latest date pipeline status(For example 30-12-2014). I want to acheive the latest status of pipeline for all rundates.

[lbendlin]

- find the latest start time for each pipeline and day.  

- find the pipeline status for that pipeline, day and time.

[lbendlin]

This is a very typical pattern. You can use the syntax sugar function LASTNONBLANKVALUE but I prefer the pedestrian two step approach

1. find the latest date

2. find the value for the latest date.

I use variables for this, I recommend you do the same.