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

You can't pass DAX code to the SQL Server

You either create a stored procedure or paste some plain SQL Code

RyanPB's avatar
RyanPB
Regular Visitor
7 years ago

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]))

  • 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