INNER JOIN

[googlogmob]

Please, help to make this T-SQL in DAX

select distinct
       cte.[ID] from cte 
inner join cte cte2 on cte.[ID]=cte2.[ID] 
 and cte2.[Group] in ('NEW';'OLD')
 and cte2.[Date] < MIN('Calendar'[Date])
 and DATEADD(DAY,91,cte2.[Date] > MAX'Calendar'[Date])
where cte.[Group] = 'Povt'

I tried code like this but returns an error

 Calculation error in 'cte' measure [Povt]: A single value was expected, but a table or several values ​​were passed.

Povt = 
VAR cte  = CALCULATE(
	                 VALUES('cte'[ID]);
	                 FILTER('cte';'cte'[Group]="Povt");	
	                 USERELATIONSHIP('Calendar'[Date];'cte'[Date]);
					 FILTER('cte';'cte'[ID] = 
					 
           CALCULATE(
	                 VALUES('cte'[ID]);
	                 FILTER('cte';'cte'[Group]IN{"NEW";"OLD"});						 
					 && 'cte'[Date] < MIN('Calendar'[Date])
					 && DATEADD('cte'[Date];91;DAY) > MAX('Calendar'[Date]));
	                 USERELATIONSHIP('Calendar'[Date];'cte'[Date]))	
		))

RETURN cte

[googlogmob]

 @Greg_Deckler, Thanks

This works

Povt = 
VAR cte  = CALCULATETABLE(
	                 VALUES('cte'[ID]);
	                 FILTER('cte';'cte'[Group]="Povt");	
	                 USERELATIONSHIP('Calendar'[Date];'cte'[Date]))
					 
VAR cte2  = CALCULATETABLE(
	                 VALUES('cte'[ID]);
                         ALL('Calendar);
	                 FILTER('cte';'cte'[Group]IN{"NEW";"OLD"}						 
					 && 'cte'[Date] < MIN('Calendar'[Date])
					 && DATEADD('cte'[Date];91;DAY) > MAX('Calendar'[Date]));
	                 USERELATIONSHIP('Calendar'[Date];'cte'[Date]))	

VAR joined = NATURALINNERJOIN(cte;cte2)
VAR result = COUNTROWS(joined)

RETURN result

[Greg_Deckler]

Sweet!

[Greg_Deckler · Accepted solution]

Use CALCULATETABLE or other function like SUMMARIZE to calculate two separate VAR's. Use NATURALINNERJOIN to do a join.

https://msdn.microsoft.com/en-us/library/dn802543.aspx