Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Hope you guys are alright. I have a question about the DirectQuery method since my database is to big to import.
Now we are using a sql statement, by using the sql report editor to show some facts about last weeks employees.
My question is,
Can i use following query directly in DirectQuery to import the right data and how does it work?
SET DATEFIRST 1 ;
WITH temp ( Account , DayOfWeek , Duration , TickID , WeekNumber , Year) AS
(
SELECT [User]
,DatePart(dw,[CreatedDateTime])
,[Minutes]
,[TicketNumber]
,DatePart("ww",[CreatedDateTime]) AS WeekNumber
,DatePart("yyyy",[CreatedDateTime]) AS Year
FROM [ksubscribers].[kasadmin].[vSDTicketNotes]
),
temp1 (Account_Closed , WKNr_Closed , Y_Closed , Amount_Closed) AS
(
SELECT [Assignee]
,DatePart("ww",[Closed]) AS WeekNumber
,DatePart("yyyy",[Closed]) AS Year
,Count(*)
FROM [ksubscribers].[kasadmin].[vSDTicket]
GROUP BY [Assignee] , DatePart("ww",[Closed]) , DatePart("yyyy",[Closed])
)
Select Account , DayOfWeek , SUM(Duration) AS Duration , TickID , COUNT(*) AS Amount , WeekNumber , Year , temp1.Amount_Closed from temp
INNER JOIN temp1 ON temp1.WKNr_Closed = WeekNumber and temp1.Y_Closed = Year and temp1.Account_Closed = Account
WHERE Duration > 0 and Account Like 'itsn%' and TickID like 'CH%' and Year = 2017
GROUP BY Account , WeekNumber , Year , DayOfWeek , TickID , temp1.Amount_Closed
Do you have any ideas?
Solved! Go to Solution.
@miltenburger wrote:
Maybe to make it more clear for you guys.
What i actually want is:
Get data from my Kaseya database (SQL), but i don't want all the tables with all the rows,
i only want data from last 7 days from several tables (This is what the query above does)
I don't have Kaseya DB at hand, per my knowledge on SQL Server, in Power BI, when using DirectQuery mode, the leading SET statement and CTE expressions are not supported, you'll have remove the SET statement and replace CTEs with derived tables.
Maybe to make it more clear for you guys.
What i actually want is:
Get data from my Kaseya database (SQL), but i don't want all the tables with all the rows,
i only want data from last 7 days from several tables (This is what the query above does)
@miltenburger wrote:
Maybe to make it more clear for you guys.
What i actually want is:
Get data from my Kaseya database (SQL), but i don't want all the tables with all the rows,
i only want data from last 7 days from several tables (This is what the query above does)
I don't have Kaseya DB at hand, per my knowledge on SQL Server, in Power BI, when using DirectQuery mode, the leading SET statement and CTE expressions are not supported, you'll have remove the SET statement and replace CTEs with derived tables.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |