CODEWITHSUNDEEP
filterpowerbidaxcalculated-columns
Lausaurine
Lausaurine

Reputation: 11

Return earliest date in Power BI column based on other column value

I am trying to create a column that returns the earliest date from the column 'update' when the column 'task' has the value 'A', for each ID.

My context is : the table i have is a history of every modifications done on a database of about 10 columns, but here I want to focus on when the task was started, so the earliest date. When the task A is done for an ID, column 'Task' takes value B, meaning task B has now started.

Table :

ID Task update (dd/mm/yyyy)
1 A 01/01/2023
1 B 02/01/2023
2 A 03/01/2023
1 A 05/01/2023

Result :

ID Task update (dd/mm/yyyy) A
1 A 01/01/2023 01/01/2023
1 B 02/01/2023 01/01/2023
2 A 03/01/2023 03/01/2023
1 A 05/01/2023 01/01/2023

The end goal is to do that with each task, then only have one row per ID, like so :

ID A B
1 01/01/2023 02/01/2023
2 03/01/2023

I first tried using only filters, so filter by Task value first, then order date descending, then keep the first row only but it doesn't work.

On another post I saw this dax but I struggle to make it work for me.

A = 
VAR MinUpdate =
   CALCULATE (
     MIN ( 'Table'[Update]),FILTER('Table','Table'[Task]="A"))

RETURN
    IF (('Table'[Update] == MinUpdate), 'Table'[Update])

It only returns the earliest date in the whole table rather than by ID.

I am a beginner in DAX and I don't really know how to make this work.

Upvotes: 1

Views: 2743

Answers (1)

Dordi
Dordi

Reputation: 778

I think it's easier to do it in Power Query M language. To do so, you can select your table then "Edit Query". Add a step in your table query in which you add the following code:

Table.Pivot(
        Table.TransformColumnTypes(#"Changed Type", {{"ID", type text}}), 
        List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"ID", type text}})[Task]), 
       "Task", 
       "update ", 
        List.Min)

What i'am using here is the pivot fucntion, you can also use the graphic tool to implement it:

enter image description here

Upvotes: 0

Related Questions