Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AFra
Helper III
Helper III

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
v-lili6-msft
Community Support
Community Support

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
v-lili6-msft
Community Support
Community Support

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.

it works nicely, thanks a lot!!! 

Nathaniel_C
Super User
Super User

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!




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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.