CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktopm
coding
coding

Reputation: 167

Power BI - Get the max of a value based on a category, status and date

I've a table that shows me the category by status and the start and end date:

coding_0-1670006514422.png

and I'm trying to create a conditional column that returns the maximum of each category and status.

I tried to create the column in M

Logic= if [Status] = "InProgress" and [Start] = List.Max(#"Sorted Rows1"[Start]) and [End] = #datetime(2999, 12, 31, 0, 0, 0) then 2 else if [Status] = "Succeeded" then 1 else 0

​​and it is working correctly except for the case of CategoryG which should only return the value of "2" from the Logic column, and instead it is returning the maximum of the Succeeded status and the InProgress status, where it should only show the InProgress status because it has a longer start date.

coding_1-1670006799161.png

I also tried creating in DAX but got the same result:

Flag DAX = SWITCH( TRUE (),
       'Table'[Start]= MAX ( 'Table'[Start] )
        && YEAR('Table'[End]) = 2999
        && 'Table'[Status] = "InProgress", 2,
        'Table'[Status] = "Failed", 1,
        'Table'[Start]= LASTNONBLANK(  'Table'[Start],1 )
        && 'Table'[Status] = "Succeeded" && YEAR('Table'[End]) <> 2999, 3,
        0
  )

Sample data 1:
CategoryName    Status  StartDate   EndDate
CategoryA   Succeeded   01/12/2022 22:31:54 02/12/2022 01:31:39
CategoryA   InProgress  02/12/2022 00:24:52 16/01/2001 00:00:00
CategoryB   InProgress  02/12/2022 01:31:40 16/01/2001 00:00:00
CategoryB   Succeeded   02/12/2022 01:31:41 02/12/2022 04:25:46
CategoryC   InProgress  02/12/2022 04:25:48 16/01/2001 00:00:00
CategoryC   Succeeded   02/12/2022 04:25:49 02/12/2022 08:23:52
CategoryD   InProgress  02/12/2022 08:23:56 16/01/2001 00:00:00
CategoryE   InProgress  02/12/2022 08:23:56 16/01/2001 00:00:00
CategoryD   Succeeded   02/12/2022 08:23:57 02/12/2022 09:51:37
CategoryE   Succeeded   02/12/2022 08:23:57 02/12/2022 09:42:21
CategoryF   InProgress  02/12/2022 09:42:35 16/01/2001 00:00:00
CategoryF   Succeeded   02/12/2022 09:42:36 02/12/2022 12:17:46
CategoryG   Succeeded   02/12/2022 12:17:52 02/12/2022 15:07:59
CategoryG   InProgress  02/12/2022 12:17:53 31/12/2999 00:00:00

Sample data 2 (with Failed): 

CategoryName    Status  StartDate   EndDate
CategoryA   Succeeded   01/12/2022 22:31:54 02/12/2022 01:31:39
CategoryA   InProgress  02/12/2022 00:24:52 16/01/2001 00:00:00
CategoryB   InProgress  02/12/2022 01:31:40 16/01/2001 00:00:00
CategoryB   Failed  02/12/2022 01:31:41 02/12/2022 04:25:46

desire output: enter image description here

Briefly, for each category I want to create a table that shows the last Status with the minimum of the StartDate and the maximum of the EndDate, to show the execution time with the last Status of each Category.

The desire table will only have one row for each category showing the last status, minimum StartDate and maximum EndDate.

Can anyone please help me in achieving this?

Thank you!

Upvotes: 0

Views: 2380

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

In your edited text, you write: "The desire table will only have one row for each category showing the last status, minimum StartDate and maximum EndDate."

That is different from your screenshot labelled desire output.

But if that is really what you want, then you can accomplish this in M-Code

  • Group by CategoryName
  • Add custom aggregations to extract the other information

As below:

   #"Grouped Rows" = Table.Group(#"Previous Step", {"CategoryName"}, {
        {"Min Start", each List.Min([StartDate]), type datetime},
        {"Max End", each List.Max([EndDate]), type datetime},
        {"Last Status", each [Status]{List.PositionOf([StartDate], List.Max([StartDate]))}, type text}})

enter image description here

Upvotes: 1

Related Questions