Forum Discussion
Find the most recent row based on multiple columns
Hello everyone,
I set up a working pbix file for this problem. Log.pbix
I am doing a log parsing on this table. The last column "Required in To Version" is the result I would like to get. Thanks in advance for any help.
The logic is:
1) For Issues same ID and FieldType = "Version", if Required or Not Required field value is "Required" when the Version value is xxx, then mark it as Yes;
e.g. for the second row, the Version V2 was set on 01-02, and this Issue #1 was set to be Required on 01-01, which means it is required on V2. Then the result is Yes.
Not sure if I explained the logic clearly..... Thanks!
| ID | ChangeTime | FieldType | From | To | Required in To Version? |
| #1 | 2018-01-01 8am | Required or Not Required | Not Required | Required | |
| #1 | 2018-01-02 9am | Version | V1 | V2 | Yes |
| #1 | 2018-01-12 9am | Required or Not Required | Required | Not Required | |
| #1 | 2018-01-13 11am | Version | V2 | V3 | No |
| #1 | 2018-01-14 10am | Required or Not Required | Not Required | Required | |
| #1 | 2018-01-15 4pm | Version | V3 | V4 | Yes |
0
2 Replies
I think maybe we could help you better if you were to explain what you want more clearly, as it is hard to understand what you are after.
hi, zxl1031
If your logic is simple to understand is that
For Issues same ID and FieldType = "Version", after ranking by ChangeTime for the same ID, the value of the previous row in column To is "Required"
then "Yes", and the value of the previous row in column To is "Not Required" then "No"?
If so, you could do these as below:
Step1:
Add a group rank column
group rank = RANKX(FILTER('Log','Log'[ID]=EARLIER('Log'[ID])),'Log'[ChangeTime],,ASC)Step2:
Add the Required in To Version column
Required in To Version = IF('Log'[FieldType]="Version",IF(CALCULATE(MAX('Log'[To]),FILTER('Log','Log'[group rank]=EARLIER('Log'[group rank])-1))="Required","Yes","No"))Result:
If not your case, please explain what you want more clearly for your logic.
Best Regards,
Lin
