cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Multiple IF taking in account previous rows

Hi,

I have this table which is an export of a discussion between two persons.

I'd like to create a 4th column that will display A, B, C or D following these rules :

- A : Time between Msg on Row N > .333

- B : Time between Msg on Row N < .000694 AND Author on Row N = Author on Row N-1

- C : Time between Msg on Row N < .333 AND Author on Row N <> Author on Row N-1

- D : Time between Msg on Row N+1 > 1

I see how easy this can be on excel. Here I guess I can use the EARLIER function to deal with the Row N / N-1 / N+1, but I don't fully get how to use it.

So far I have :

Type =

Type=if('2019'[Time Between Msg]>0,333;"A";if('2019'[Time Between Msg]<0,000694 && ???

Thanks in advance for any help!
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Multiple IF taking in account previous rows

hi @AFra

Try this way as below:

Step1:

Add an index column in table.

Step2:

Use this formula to get the expected output.

``````Result =
VAR lastrow =
CALCULATE (
MAX ( 'Table'[Author] ),
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
)
RETURN
VAR nextrowTimebetweenMsg =
CALCULATE (
MAX ( 'Table'[Time Between Msg (days)] ),
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
)
RETURN
IF (
'Table'[Time Between Msg (days)] > 0.333,
"A",
IF (
'Table'[Time Between Msg (days)] < 0.000694
&& 'Table'[Author] = lastrow,
"B",
IF (
'Table'[Time Between Msg (days)] < 0.333
&& 'Table'[Author] <> lastrow,
"C",
IF ( nextrowTimebetweenMsg > 1, "D" )
)
)
)``````

and here is sample pbix file, please try it.

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Highlighted
Super User IV

## Re: Multiple IF taking in account previous rows

Hi @AFra ,

If you would copy and paste this excel right into the post, or into the code panel (</> see the symbol above) we would be able to help you. BTW, earlier is not great, use a variable and a return.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User!

Highlighted
Helper I

## Re: Multiple IF taking in account previous rows

Hi @Nathaniel_C ,

I'm not very familiar with the variables and returns neither 🙄

Here's the copy/paste of data

 Date Time Between Msg (days) Author 1/1/19 2:00 AM 0,04066 XA 1/1/19 7:44 AM 0,23928 XA 1/1/19 12:05 PM 0,11520 GF 1/1/19 12:06 PM 0,00059 GF 1/1/19 1:12 PM 0,04571 XA 1/1/19 1:12 PM 0,00044 GF 1/1/19 3:37 PM 0,01402 GF 9/1/19 2:06 PM 7,93672 GF 10/1/19 1:41 AM 0,25485 GF 10/1/19 1:47 AM 0,00468 XA 10/1/19 6:27 AM 0,02689 GF 10/1/19 6:27 AM 0,00022 GF 12/1/19 1:31 PM 2,28171 XA 12/1/19 1:31 PM 0,00007 XA 19/1/19 5:19 PM 0,01395 XA 19/1/19 5:22 PM 0,00044 GF 19/1/19 6:29 PM 0,02322 GF 19/1/19 6:29 PM 0,00015 XA 19/1/19 6:52 PM 0,01575 GF 19/1/19 8:15 PM 0,02640 GF

Thanks a lot!!

Highlighted
Community Support

## Re: Multiple IF taking in account previous rows

hi @AFra

Try this way as below:

Step1:

Add an index column in table.

Step2:

Use this formula to get the expected output.

``````Result =
VAR lastrow =
CALCULATE (
MAX ( 'Table'[Author] ),
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
)
RETURN
VAR nextrowTimebetweenMsg =
CALCULATE (
MAX ( 'Table'[Time Between Msg (days)] ),
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
)
RETURN
IF (
'Table'[Time Between Msg (days)] > 0.333,
"A",
IF (
'Table'[Time Between Msg (days)] < 0.000694
&& 'Table'[Author] = lastrow,
"B",
IF (
'Table'[Time Between Msg (days)] < 0.333
&& 'Table'[Author] <> lastrow,
"C",
IF ( nextrowTimebetweenMsg > 1, "D" )
)
)
)``````

and here is sample pbix file, please try it.

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I

## Re: Multiple IF taking in account previous rows

it works nicely, thanks a lot!!!

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.