Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 =
Solved! Go to Solution.
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
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
it works nicely, thanks a lot!!!
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!
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!!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |