SQL outer apply with top 1 table

[Anonymous]

Hi

Is there a way to convert this query for the service? It works well on the Desktop version but not in the service

declare @From date
declare @to date
SELECT t1.*,t2.*,t3.*
FROM Fact f

Cross apply
(
select top 1
from dimension1 d1
where f.id=d1.id
and datefrom >=@From  and dateto <=@To
order by datefrom desc) t1

outer apply

(select top 1
from dimension2 d2
where f.id2=d2.id
and datefrom >=@From  and dateto <=@To
order by datefrom desc) t2

outer apply

(select top 1
from dimension2 d2
where f.id3=d2.id
and datefrom >=@From  and dateto <=@To
order by datefrom desc) t3

[lbendlin]

As I understand it you only need it for table valued functions.  CTEs are convenience constructs for subqueries, and joins work (mostly) the same way as applies.

SQL Server CROSS APPLY and OUTER APPLY (mssqltips.com)

[Anonymous]

I don't understand your response, I need to convert the query above to powerbi, there are other ways of doing apply type functions, but neither work with power bi in the format of the query.

[lbendlin]

I don't see any functions. You should be able to do the same with a standard left join.

[Anonymous]

These are table expressions being joined, so PBI needs to dynamically filter the apply expressions at run time for top 1's. It's not the same as a LEFT JOIN  to a table