Forum Discussion
select with direct query
Hi,
This seems good although now the issue is using your method I would like to add an extra column which is a calculation i.e.Select DateKey, [Quarter],Price
,sum(case when right(str(DateKey,8),4)=qd.StartMonthDayKey then Price else null end)over( order by DateKey) qPrice
, CalculationPerformance = Price/qPrice - 1
from tblFact f join #tblQuarterlyDate qd on f.Quarter=qd.QuarterKey
Thank you
Hey Anonymous ,
from your post, it seems that there is a DDL part and a DML part. You can use only SELECT ... or EXECUTE PROCEDURE ... statements as a native query.
Because of this, you have to rewrite your SQL code to something like this
SELECT
...
FROM tblFact f join
(SELECT 1 as QuarterKey, '0105' as StartMonthDayKey
UNION
SELECT 1 as QuarterKey, '0105' as StartMonthDayKey
UNION
...
) as qd on f.Quarter=qd.QuarterKey
You also have to be aware that SQL code written in something like SQL Server Management Studio or Azure Data Studio, does not necessarily represent one SQL Object like a view, as often different statements are separated by a semicolon, or should be separated by a semicolon. Sometimes the SQL code is separated into multiple batches by using GO.
What can be used inside Power BI without any modification is a single statement that starts with SELECT.
Regards,
Tom
Hi,
This seems good although now the issue is using your method I would like to add an extra column which is a calculation i.e.Select DateKey, [Quarter],Price
,sum(case when right(str(DateKey,8),4)=qd.StartMonthDayKey then Price else null end)over( order by DateKey) qPrice
, CalculationPerformance = Price/qPrice - 1
from tblFact f join #tblQuarterlyDate qd on f.Quarter=qd.QuarterKey
Thank you