Forum Discussion

RyanPB's avatar
RyanPB
Regular Visitor
7 years ago
Solved

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[Produ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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

LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

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
RyanPB's avatar
RyanPB
Regular Visitor
7 years ago

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.

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    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