Forum Discussion
Iterate rows in a table based on multiple columns from another table
Hi,
I have below table with the following info. I want to know if each of these rows is being retreated or treated for the first time. So basically a logical "yes" or "no". It has to be compared to another table which will have similar information. Both the tables will have multiple rows for each ID and there will be thousands of ID. Table 2 will have more records than table 1. Below is a sample info for one of the IDs.
Table 1:
| Date.1 | ID.1 | Type.1 |
| 31/1/24 | 1 | 6 |
| 24/1/24 | 1 | 6 |
| 17/1/24 | 1 | 8 |
| 10/1/24 | 1 | 8 |
| 27/12/23 | 1 | 1 |
| 20/12/23 | 1 | 33 |
Table 2: (Sorted by ID.2 (asec) and Date.2 (desc)
| Date.2 | ID.2 | Type.2 | Discharge |
| 2/2/24 | 1 | 6 | False |
| 31/1/24 | 1 | 6 | False |
| 26/1/24 | 1 | 6 | False |
| 24/1/24 | 1 | 8 | False |
| 24/1/24 | 1 | 6 | False |
| 19/1/24 | 1 | 8 | False |
| 17/1/24 | 1 | 8 | False |
| 12/1/24 | 1 | 8 | False |
| 10/1/24 | 1 | 8 | False |
| 3/1/24 | 1 | 1 | True |
| 27/12/23 | 1 | 33 | False |
| 27/12/23 | 1 | 1 | False |
| 20/12/23 | 1 | 33 | False |
So, I want to know if each of the row in table is a yes or no by
- Check if it is a new record by filtering table 2 for all records with id.1 = id.2 and type.1 = type.2 and date.2 < date.1. If the filtered table does not return any records, then it is a yes.
- If there is any filtered records, check for below conditions.
- if the record with latest date (Date.2) has discharge = "TRUE" then it is yes else
- If date.1 - date.2 > 7 then it is yes
- else no
The process has to be repeated for each row in table 1.
Expected Output:
| Date.1 | ID.1 | Type.1 | Status |
| 31/1/24 | 1 | 6 | no |
| 24/1/24 | 1 | 6 | yes |
| 17/1/24 | 1 | 8 | no |
| 10/1/24 | 1 | 8 | yes |
| 27/12/23 | 1 | 1 | yes |
| 20/12/23 | 1 | 33 | yes |
Many thanks,
Kaavya
Hi Kaavya ,
amustafa's solution is right , but you need to transform Date.1 type to Date . When I used to use the datediff function with a text type, it would display a numeric error.Here are my results:
Below is my table1:
Below is my table2:
The following DAX might work for you:
Status = var lat = FILTER('Table 2','Table 2'[ID.2] = 'Table 1'[ID.1] && 'Table 2'[Type.2] = 'Table 1'[Type.1] && 'Table 2'[Date.2] < 'Table 1'[Date.1]) var A = CALCULATE(MAX('Table 2'[Date.2]),lat) var dis = SELECTEDVALUE('Table 2'[Discharge],A) RETURN IF(ISBLANK(A),"Yes",IF(dis = "True","yes",IF(DATEDIFF(A,'Table 1'[Date.1],DAY) > 7 , "yes","no")))The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Here's how you can create a Status DAX calculated column in your Table1.
Status = VAR latestRecord = CALCULATE(MAX(Table2[Date.2]), FILTER(Table2, Table2[ID.2] = Table1[ID.1] && Table2[Type.2] = Table1[Type.1] && Table2[Date.2] < Table1[Date.1])) VAR isDischarged = SELECTEDVALUE(Table2[Discharge], latestRecord) RETURN IF(ISBLANK(latestRecord), "yes", IF(isDischarged = TRUE, "yes", IF(DATEDIFF(latestRecord, Table1[Date.1], DAY) > 7, "yes", "no")))Hi Amustafa,
thank you so much for your reply. It works great but the record against 17/1/23 should be a no as well. Because the date difference is less than 7 when compared to date 12/1/23 the second table.
Hi Kaavya ,
amustafa's solution is right , but you need to transform Date.1 type to Date . When I used to use the datediff function with a text type, it would display a numeric error.Here are my results:
Below is my table1:
Below is my table2:
The following DAX might work for you:
Status = var lat = FILTER('Table 2','Table 2'[ID.2] = 'Table 1'[ID.1] && 'Table 2'[Type.2] = 'Table 1'[Type.1] && 'Table 2'[Date.2] < 'Table 1'[Date.1]) var A = CALCULATE(MAX('Table 2'[Date.2]),lat) var dis = SELECTEDVALUE('Table 2'[Discharge],A) RETURN IF(ISBLANK(A),"Yes",IF(dis = "True","yes",IF(DATEDIFF(A,'Table 1'[Date.1],DAY) > 7 , "yes","no")))The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous Xianda Tag,
thank you so much for your reply.
i will give it a go.
Also is it possible to do this in power query? If so, can you please help with that.
thanks
kaavya
