Forum Discussion
Need help in creating SQL query which gives same result as created using calculated table
I have created a calculated table using the "New Table" option from Modeling menu on the Ribbon in power BI.
NewCalTable = UNION(SELECTCOLUMNS(SalesInfo,"Type", "Sold", "Product", SalesInfo[Product], "SaleDate",SalesInfo[SoldDate]), SELECTCOLUMNS(SalesInfo, "Type","Pending Sale","Product", SalesInfo[Productl], "SaleDate", SalesInfo[SoldDate]))
I want to create the same query as shown above using SQLQuery,
Get Data -->SQL Server --> give server and Dabase details --> Advanced Options ->SQL statement.
When giving the above query in SQL statement , it is throwing the error.
Details: "Microsoft SQL: Incorrect syntax near the keyword 'UNION'."
Hi RyanPB,
The SQL statement could be like below. I would suggest you use DAX instead.
SELECT 'Daily Sales' AS Type,
product AS Product,
CONVERT(VARCHAR, solddate, 23) AS SaleDate
FROM salesinfo
UNION
SELECT 'Weekly Sales' AS Type,
product AS Product,
CONVERT(VARCHAR, Dateadd(day, - Datepart(weekday, solddate) + 1, solddate
), 23)
+ '-'
+ CONVERT(VARCHAR, Dateadd(day, 7 - Datepart(weekday, solddate), solddate
), 23)
AS SaleDate
FROM salesinfo;Best Regards,
Dale
7 Replies
You can't pass DAX code to the SQL Server
You either create a stored procedure or paste some plain SQL Code
LivioLanzo - Yes, i need help in creating SQL Query for my DAX code. Thanks.
NewCalTable = UNION(SELECTCOLUMNS(SalesInfo,"Type", "Sold", "Product", SalesInfo[Product], "SaleDate",SalesInfo[SoldDate]),
SELECTCOLUMNS(SalesInfo, "Type","Pending Sale","Product", SalesInfo[Product], "SaleDate", SalesInfo[SoldDate]))should be something like this but you are selecting the same entire table twice basically, i find it hard to understand what you're tryin to do RyanPB
SELECT "Sold" as Type, i.Product, i.SoldDate FROM SalesInfo i UNION ALL SELECT "Pending Sales" as Type, s.Product, s.SoldDate FROM SalesInfo s
