Hi team!
I hope you are doing great! I thanks for your support.
I need your help to figure out how to create a logic that results in the "Result" column, considering the number_of_people,
sequence_number,
payment_date,
modified_date,
creation_date,
Filter Address=1,
status=ERROR_TIMBRE.
If the sequence number is the same for an employee, keep a line that meets the Address Filter = 1 and status = ERROR_TIMBRE, and use the most recent date from any of the 3 date columns. If the employee has different sequence numbers and some are repeated, do the same, making sure to keep the line with the most recent date compared to the others
| number_of_people | sequence_number | modified_date | payment_date | creation_date | Filter Address | status | Result |
| 32000127 | 1 | 12/28/2023 | 6/3/2024 | 6/3/2024 | 0 | ERROR_TIMBRE | Remove |
| 32000127 | 1 | 1/4/2024 | 6/3/2024 | 6/3/2024 | 1 | ERROR_TIMBRE | Remove |
| 32000127 | 1 | 5/31/2024 | 6/3/2024 | 6/3/2024 | 1 | ERROR_TIMBRE | Keep |
| 32000185 | 11 | 12/26/2023 | 5/15/2024 | 6/7/2024 | 0 | ERROR_TIMBRE | Remove |
| 32000185 | 11 | 12/26/2023 | 5/15/2024 | 6/12/2024 | 0 | ERROR_TIMBRE | Remove |
| 32000185 | 11 | 12/26/2023 | 5/15/2024 | 6/13/2024 | 0 | Emitido | Remove |
| 32000185 | 11 | 6/12/2024 | 5/15/2024 | 6/7/2024 | 1 | ERROR_TIMBRE | Remove |
| 32000185 | 11 | 6/12/2024 | 5/15/2024 | 6/12/2024 | 1 | ERROR_TIMBRE | Keep |
| 32000185 | 11 | 6/12/2024 | 5/15/2024 | 6/13/2024 | 1 | Emitido | Remove |
| 32000185 | 12 | 12/26/2023 | 5/30/2024 | 6/7/2024 | 0 | ERROR_TIMBRE | Remove |
| 32000185 | 12 | 12/26/2023 | 5/30/2024 | 6/12/2024 | 0 | ERROR_TIMBRE | Remove |
| 32000185 | 12 | 12/26/2023 | 5/30/2024 | 6/13/2024 | 0 | Emitido | Remove |
| 32000185 | 12 | 6/12/2024 | 5/30/2024 | 6/7/2024 | 1 | ERROR_TIMBRE | Remove |
| 32000185 | 12 | 6/12/2024 | 5/30/2024 | 6/12/2024 | 1 | ERROR_TIMBRE | Keep |
| 32000185 | 12 | 6/12/2024 | 5/30/2024 | 6/13/2024 | 1 | Emitido | Remove |
| 32000185 | 13 | 12/26/2023 | 6/14/2024 | 6/13/2024 | 0 | Emitido | Remove |
| 32000185 | 13 | 6/12/2024 | 6/14/2024 | 6/13/2024 | 1 | Cancelado | Remove |
| 32000185 | 14 | 12/26/2023 | 6/28/2024 | 6/27/2024 | 0 | Emitido | Remove |
| 32000185 | 14 | 6/12/2024 | 6/28/2024 | 6/27/2024 | 1 | Emitido | Remove |
| Table name: Secuencias | |||||||
| Expected results | |||||||
| number_of_people | sequence_number | modified_date | payment_date | creation_date | Filter Address | status | Result |
| 32000127 | 1 | 5/31/2024 | 6/3/2024 | 6/3/2024 | 1 | ERROR_TIMBRE | Keep |
| 32000185 | 11 | 6/12/2024 | 5/15/2024 | 6/12/2024 | 1 | ERROR_TIMBRE | Keep |
| 32000185 | 12 | 6/12/2024 | 5/30/2024 | 6/12/2024 | 1 | ERROR_TIMBRE | Keep |
Thanks for the reply from lbendlin , please allow me to provide another insight:
Hi @MichelleRA_24 ,
Here are the steps you can follow：
1. Create calculated column.
Result_test =
var _modifieddate=
MAXX(
FILTER(ALL('Test'),
'Test'[number_of_people]=EARLIER('Test'[number_of_people])&&'Test'[Filter Address]=1&&'Test'[status]="ERROR_TIMBRE"&&
'Test'[modified_date]<=TODAY()),[modified_date])
var _count=
COUNTX(
FILTER(ALL('Test'),[number_of_people]=EARLIER('Test'[number_of_people])&&'Test'[sequence_number]=EARLIER('Test'[sequence_number])&&[modified_date]=_modifieddate),[number_of_people])
var _creationdate=
MAXX(
FILTER(ALL('Test'),
'Test'[number_of_people]=EARLIER('Test'[number_of_people])&&'Test'[Filter Address]=1&&'Test'[status]="ERROR_TIMBRE"&&'Test'[sequence_number]=EARLIER('Test'[sequence_number])&&
'Test'[creation_date]<=TODAY()),[creation_date])
return
IF(
_count=1&&'Test'[modified_date]=_modifieddate,"Keep",
IF( _count>1&&'Test'[modified_date]=_modifieddate&&'Test'[creation_date]=_creationdate,"Keep",BLANK()))2. Create calculated table.
Table =
FILTER(
'Test',[Result_test]="Keep")3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks for the reply from lbendlin , please allow me to provide another insight:
Hi @MichelleRA_24 ,
Here are the steps you can follow：
1. Create calculated column.
Result_test =
var _modifieddate=
MAXX(
FILTER(ALL('Test'),
'Test'[number_of_people]=EARLIER('Test'[number_of_people])&&'Test'[Filter Address]=1&&'Test'[status]="ERROR_TIMBRE"&&
'Test'[modified_date]<=TODAY()),[modified_date])
var _count=
COUNTX(
FILTER(ALL('Test'),[number_of_people]=EARLIER('Test'[number_of_people])&&'Test'[sequence_number]=EARLIER('Test'[sequence_number])&&[modified_date]=_modifieddate),[number_of_people])
var _creationdate=
MAXX(
FILTER(ALL('Test'),
'Test'[number_of_people]=EARLIER('Test'[number_of_people])&&'Test'[Filter Address]=1&&'Test'[status]="ERROR_TIMBRE"&&'Test'[sequence_number]=EARLIER('Test'[sequence_number])&&
'Test'[creation_date]<=TODAY()),[creation_date])
return
IF(
_count=1&&'Test'[modified_date]=_modifieddate,"Keep",
IF( _count>1&&'Test'[modified_date]=_modifieddate&&'Test'[creation_date]=_creationdate,"Keep",BLANK()))2. Create calculated table.
Table =
FILTER(
'Test',[Result_test]="Keep")3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
You posted this in the DAX section. Is it ok to do this via Power Query instead?
making sure to keep the line with the most recent date compared to the others
That is a very ambiguous requirement. Your sample data only has two rows per group. Please provide better sample data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZTLCoMwEEX/xbWQZJKo6xYXpZRC6E6kSJuFUGsXpd/fBBEf0TxsF4GJymHuPWBRRBQwxgTSKI6IPoAgQ4CBqkuCqB7ZdMTq5EKcxfVyOO1Erq5CNu1HRmVs8hBbQIxGEkLjiJJg3FHK1wiWcf1ZnzXps3JE+MBLQ8J6EfWrfyOnSpr6Xd9bO228x0piHyE+wOF5iBMLb+rYlRbM7ij+SbCDuEWwCxkmGIz+FhP7C7YDwwU7eGGC6bw7RWCbu6Oz1dZgerV99bzJR2XDMXO57rfa8SANWm7e9xpsubfyCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [number_of_people = _t, sequence_number = _t, modified_date = _t, payment_date = _t, creation_date = _t, #"Filter Address" = _t, status = _t, Result = _t]),
#"Filtered Rows" = Table.SelectRows(Source, each ([status] = "ERROR_TIMBRE") and ([Filter Address] = "1")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"modified_date", type date}, {"payment_date", type date}, {"creation_date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Max Date", (k)=> let f = Table.SelectRows(#"Changed Type", each [number_of_people]=k[number_of_people] and [sequence_number]=k[sequence_number])
in
List.Max(List.Combine({f[modified_date],f[payment_date],f[creation_date]})))
in
#"Added Custom"
Hi Ibendlin, thanks for helping me, I don´t have problem using Power query
This is all I have; there are more than 100 employees, but they have the same data.
In this case, the only difference is that one column may be missing dates, which can happen.
So, the logic is the same: each employee must have a unique sequence and be the most recent among the three dates. The rows should also maintain Filter Address=1 and Status=ERROR_TIMBRE. For the rows that meet these criteria, indicate 'Keep', and for those that do not, indicate 'Remove'.
| number_of_people | sequence_number | modified_date | payment_date | creation_date | Filter Address | status | Result | 32000120 | 15 | 12/26/2023 | 6/14/2024 | 6/13/2024 | 0 | ERROR_TIMBRE | 32000120 | 15 | 1/4/2024 | 6/14/2024 | 6/13/2024 | 1 | ERROR_TIMBRE | Keep | 32000120 | 16 | 12/26/2023 | 6/28/2024 | 6/27/2024 | 1 | ERROR_TIMBRE | 32000120 | 16 | 1/4/2024 | 6/28/2024 | 6/27/2024 | 1 | ERROR_TIMBRE | Keep | 32001297 | 23 | 2/22/2024 | 6/7/2024 | 0 | ERROR_TIMBRE | 32001297 | 23 | 2/22/2024 | 6/12/2024 | 1 | ERROR_TIMBRE | 32001297 | 23 | 2/25/2024 | 6/7/2024 | 1 | ERROR_TIMBRE | 32001297 | 23 | 2/25/2024 | 6/12/2024 | 1 | ERROR_TIMBRE | Keep | 32001297 | 29 | 2/22/2024 | 6/14/2024 | 6/14/2024 | 0 | Emitido | 32001297 | 29 | 2/25/2024 | 6/14/2024 | 6/14/2024 | 1 | ERROR_TIMBRE | Keep | 32001297 | 30 | 2/22/2024 | 6/21/2024 | 6/21/2024 | 1 | ERROR_TIMBRE | 32001297 | 30 | 2/25/2024 | 6/21/2024 | 6/21/2024 | 1 | ERROR_TIMBRE | Keep | 32001297 | 31 | 2/22/2024 | 6/28/2024 | 6/28/2024 | 0 | Emitido | 32001297 | 31 | 2/25/2024 | 6/28/2024 | 6/28/2024 | 1 | ERROR_TIMBRE | Keep | Expected results | number_of_people | sequence_number | modified_date | payment_date | creation_date | Filter Address | status | Result | 32000120 | 15 | 1/4/2024 | 6/14/2024 | 6/13/2024 | 1 | ERROR_TIMBRE | Keep | 32000120 | 16 | 1/4/2024 | 6/28/2024 | 6/27/2024 | 1 | ERROR_TIMBRE | Keep | 32001297 | 23 | 2/25/2024 | 6/12/2024 | 1 | ERROR_TIMBRE | Keep | 32001297 | 29 | 2/25/2024 | 6/14/2024 | 6/14/2024 | 1 | ERROR_TIMBRE | Keep | 32001297 | 30 | 2/25/2024 | 6/21/2024 | 6/21/2024 | 1 | ERROR_TIMBRE | Keep | 32001297 | 31 | 2/25/2024 | 6/28/2024 | 6/28/2024 | 1 | ERROR_TIMBRE | Keep |
and be the most recent among the three datesThat's the ambiguous statement - I think what you really want is "the most recent date in one of the columns if the other column have matching dates". Or something like that - sequence number 23 seems to be even more complex.
Helpful resources
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
