Multiple IF Measure with one to many relation

[Dexter1088]

Looking for a measure that will tell me if there are any issues open on that item and also if any like issue has occured in the last 20 days.

Tried this, but it's not catching everything. any suggestions? ty

IF(MAX(TICKET TABLE[Unique_Tie])<>MAX(ISSUE TABLE[Unique_Tie]) && MIN(ISSUE TABLE[Age of issue])<0, "NO",
IF(MAX(TICKET TABLE[Unique_Tie])=MAX(ISSUE TABLE[Unique_Tie]) && MAX(ISSUE TABLE[Age of issue])<20, "NO"
,"YES")))

		ISSUE TABLE
Item	Issue	Unique_Tie	Age of issue	Date of issue
1	A	1_A	21	8/23/2024
2	A	2_A	25	8/19/2024
3	B	3_B	12	9/1/2024
1	B	1_B
2	A	2_A	55	7/20/2024

		TICKET TABLE
Item	Issue	Unique_Tie	Desired Outcome
1	A	1_A	YES
2	A	2_A	NO
3	B	3_B	YES

[v-bofeng-msft]

Hi @Dexter1088 ,

Could you please tell me:

Are you looking to retrieve the 'TICKET TABLE' based on the 'ISSUE TABLE'? If so, are you grouping by Item to check whether the group contains any records where the 'Age of issue' is less than 20 or is blank?

Additionally, for the 'Ticket Table,' on what conditions do you retain 'Issue' and 'Unique_Tie'? Since an Item may have both Issue "A' and "B", as well as Unique_Tile values like "1_A" and "1_B", do you only need to retain the first record's Issue and UniqueTile for that group?

Best Regards,
Bof

[ryan_mayu]

not clear about the logic? what about 1_B?

why 2_A is NO? I think it's 25 days which is more than 20

[Dexter1088]

Item 1 Issue B is still active because it's blank
Item 2 Issue A is a NO because the issue cleared over 20 days ago