Excel - How to replace the RELATEDTABLE function where many-to-many rel. are not allowed

Hi,

I am using Excel 2016 and I have two tables in my data model. Within one table, I am trying to do a DAX calculation where one of the conditions is that the column value in both tables are the same, basically an INDEX MATCH within a COUNTIF. The problem is, these column values are not unique in either table (many-to-many relationship). I can do the Excel formula quite easily (which can be found in the Desired Output sheet), I just want to replicate it in DAX.

I have tried the following, but it won't work as I don't have a related table. I would like to use the same structure if possible and replace the RELATEDTABLE function with something else so I can follow the logic. Would someone please be able to have a look at my Excel file and let me know what I need to use for this?

= 
IF(
	[Resource] = "SME1",
		CALCULATE(
					COUNTROWS(Table_Rev),
		    			FILTER(
						    	Table_Rev,
		    						Table_Rev[SME1 Rem]=0
		    						&&
		    						Table_Rev[Consolidate]=FALSE() 		
							),
					RELATEDTABLE(Table_Rev)
					)
	,0)

Excel file