Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RyanPB
Regular Visitor

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.

 

sqlquery.png

 

When giving the above query in SQL statement  , it is throwing the error.

Details: "Microsoft SQL: Incorrect syntax near the keyword 'UNION'."

 

1 ACCEPTED 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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-jiascu-msft
Employee
Employee

Hi @RyanPB,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LivioLanzo
Solution Sage
Solution Sage

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.