Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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'."
Solved! Go to Solution.
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
Hi @RyanPB,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
You can't pass DAX code to the SQL Server
You either create a stored procedure or paste some plain SQL Code
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@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
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@LivioLanzo - Please see my updated DAX function to create a table.
I am using DAX function WEEKDAY to calculate weekly dates which i could not able to convert to SQL statement, i tried to use functions from https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017 but no luck...
Basically my DAX function does the below steps.
1)create an new column "Type" in the newly created table and the column "Type" is not available in the table SalesInfo.
2)Map with the columns product and soldDate from SalesInfo table.I want to map for weekly dates and daily dates.
NewCalTable = UNION(SELECTCOLUMNS(SalesInfo,"Type", "Daily Sales", "Product", SalesInfo[Product], "SaleDate",SalesInfo[SoldDate]), SELECTCOLUMNS(SalesInfo, "Type","Weekly Sales","Product", SalesInfo[Product], "SaleDate", SalesInfo[SoldDate] - ( WEEKDAY ( SalesInfo[SoldDate], 1 ) - 1 ) & "-" & SalesInfo[SoldDate] + 7 - WEEKDAY ( SalesInfo[SoldDate], 1 ), ))
In the above DAX function, Type is newly created column in the table we are creating which holds the values Daily Sales and Weekly Sales.
Note: I don't have permission to CREATE TABLE in my database using SQL Query.
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
Hi @RyanPB
why don't you try to do it via Power Query and take advantage of query folding? it should be more managable on your side
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |