cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Multiple IF taking in account previous rows

Hi, 

 

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

 

Capture.PNG

 

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
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.

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
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

 

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

 

Thanks a lot!!

Highlighted
Community Support
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.

View solution in original post

Highlighted
Helper I
Helper I

Re: Multiple IF taking in account previous rows

it works nicely, thanks a lot!!! 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

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.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors