Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all,
Thanks for taking a look at the question!
I've got a View which contains a recursive CTE to generate dates between a given start and end date.
WITH D AS ( SELECT CAST(DATEADD(month, DATEDIFF(month, 0, DATEADD(m, 0 - 12, GETUTCDATE())), 0) AS DATETIME) as DayDate UNION ALL SELECT DATEADD(day, 1, DayDate) FROM D WHERE DayDate < CAST(DATEADD(month, DATEDIFF(month, 0, GETUTCDATE()), 0) AS DATETIME) )
There's some other SQL code that joins onto that CTE.
Usually when executing this query I'd include
OPTION (MAXRECURSION 0)
at the end of the query, if you don't SQL Server will error.
I'm porting this query to Power BI but can't find a way to constraint the MAXRECURSION option like I would in a standard SQL statement.
I can't include this in the View because it'll error (just like an OrderBy)
I can't see a way to edit the query once I've created a SQL Server Data Set querying the View
When I navigate though 'Get Data' -> SQL Server -> Advanced Options (DirectQuery ticked) then enter a query such as
SELECT * FROM MyView OPTION(MAXRECURSION 0)
The query executes and you see a result set, but then when I click Load to create the data set I get an error stating: "Microsoft SQL: Incorrect syntax near the keyword 'OPTION'."
Any ideas as to what my options are here, and if there's a best practice for this kind of query?
Thanks in advance & kind regards,
Rob
Solved! Go to Solution.
You may refer to the post below that makes use of OPENROWSET.
https://community.powerbi.com/t5/Desktop/Calling-SQL-stored-procedure-with-Direct-Query/td-p/224831
You may refer to the post below that makes use of OPENROWSET.
https://community.powerbi.com/t5/Desktop/Calling-SQL-stored-procedure-with-Direct-Query/td-p/224831
The link you specified does not have a sloution.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
69 | |
64 | |
51 | |
36 | |
26 |
User | Count |
---|---|
85 | |
55 | |
45 | |
44 | |
36 |