Forum Discussion
Solved
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...
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.
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.