Iterate rows in a table based on multiple columns from another table

[Kaavya]

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

1. 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.
2. If there is any filtered records, check for below conditions.  
   1. if the record with latest date (Date.2) has discharge = "TRUE" then it is yes else
   2. If date.1 - date.2 > 7 then it is yes 
   3. 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 

[Anonymous · Accepted solution]

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.

[Kaavya]

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

[amustafa]

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")))

[Kaavya]

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.