cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
googlogmob Member
Member

INNER JOIN

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: INNER JOIN

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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


3 REPLIES 3
Super User
Super User

Re: INNER JOIN

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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
googlogmob Member
Member

Re: INNER JOIN

 @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

 

Super User
Super User

Re: INNER JOIN

Sweet!


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!