topic Re: Compare two rows and return the one with the earliest date Power Bi in DAX Commands and Tips

Compare two rows and return the one with the earliest date Power Bi

MikiMihaela27 — Tue, 29 Nov 2022 14:51:56 GMT

Hi,

I have a table called "Jira tickets", in which I have multiple rows for the same ticket, as you can see below in the sample data:

If we take for example Ticket 1, how can I compare the rows to take only the first Sprint when this ticket was in status "Done", based on the Status date? In this case, to have a calculated column that will say "include" for Sprint 7 (08-Sep-22) and "exclude" for Sprint 8 (04-Oct-22). I will have to do the same thing for the status "To Do".

Thank you very much! 😊

Re: Compare two rows and return the one with the earliest date Power Bi

FreemanZ — Tue, 29 Nov 2022 15:48:29 GMT

how to handle the status of In Progress?

Re: Compare two rows and return the one with the earliest date Power Bi

tamerj1 — Tue, 29 Nov 2022 17:10:13 GMT

Hi MikiMihaela27
Please try

Include/Exclude = VAR CurrentStatus = 'Jira tickets'[Status] VAR CurrentDate = 'Jira tickets'[Status Date] VAR CurrentKeyTable = CALCULATETABLE ( 'Jira tickets', ALLEXCEPT ( 'Jira tickets', 'Jira tickets'[Key] ) ) VAR FilteredTable = FILTER ( CurrentKeyTable, 'Jira tickets'[Status] = "Done" ) VAR FirstDone = MINX ( FilteredTable, 'Jira tickets'[Status Date] ) RETURN IF ( CurrentStatus = "Done", IF ( CurrentDate = FirstDone, "Include", "Exclude" ) )

Re: Compare two rows and return the one with the earliest date Power Bi

FreemanZ — Wed, 30 Nov 2022 04:14:31 GMT

i tried, but unable to reach the last step, check if a row exists in another intermediate table.

the code for the intermediate table physically :

Table =

VAR table1 =

SUMMARIZE(

JiraTickets,

JiraTickets[Key],

JiraTickets[Status]

)

VAR table2 =

ADDCOLUMNS(

table1,

"MinDate",

CALCULATE(MIN(JiraTickets[StatusDate]))

)

RETURN Table2

Re: Compare two rows and return the one with the earliest date Power Bi

v-yadongf-msft — Thu, 01 Dec 2022 09:40:11 GMT

Hi MikiMihaela27 ,

Please create following column:

Column = var min_date = MINX(FILTER('Table','Table'[Key] = EARLIER('Table'[Key]) && 'Table'[Status] = EARLIER('Table'[Status])),[Status Date]) var max_date = MAXX(FILTER('Table','Table'[Key] = EARLIER('Table'[Key]) && 'Table'[Status] = EARLIER('Table'[Status])),[Status Date]) var count_status = CALCULATE(COUNT('Table'[Status]),FILTER('Table','Table'[Key] = EARLIER('Table'[Key]) && 'Table'[Status] = EARLIER('Table'[Status]))) return SWITCH( TRUE(), 'Table'[Status Date] = min_date && count_status = 2, "include", 'Table'[Status Date] = max_date && count_status = 2, "exclude" )

I think this is the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Compare two rows and return the one with the earliest date Power Bi

MikiMihaela27 — Fri, 02 Dec 2022 10:06:23 GMT

Hi tamerj1

Thank you for your message! It really helped me a lot! In fact, I adjusted a bit the logic to display "include/exclude" for both 'To Do' and 'Done' statuses:

IncludevsExclude =
VAR CurrentStatus = 'Jira tickets'[Status]
VAR CurrentDate = 'Jira tickets'['Jira tickets' Status Date]
VAR CurrentKeyTable =
CALCULATETABLE (
'Jira tickets',
ALLEXCEPT ( 'Jira tickets', 'Jira tickets'[Key])
)
VAR FilteredTableDone =
FILTER ( CurrentKeyTable, 'Jira tickets'[Status] = "Done" )
VAR FilteredTableToDo =
FILTER ( CurrentKeyTable, 'Jira tickets'[Status] = "To Do" )
VAR FirstDone =
MINX ( FilteredTableDone, 'Jira tickets' [Status Date])
VAR FirstToDo =
MINX ( FilteredTableToDo, 'Jira tickets' [Status Date])
RETURN
IF (
CurrentStatus = "Done",
IF ( CurrentDate = FirstDone, "Include", "Exclude" ),
IF(
CurrentStatus = "To Do",
IF ( CurrentDate = FirstToDo, "Include", "Exclude" )
))

Thanks again! 😊