Skip to main content

Notifications

Power Automate - Building Flows
Answered

Running a query against a Power BI dataset / The syntax for 'SELECT' is incorrect

Like (1) ShareShare
ReportReport
Posted on 4 Oct 2024 15:01:37 by 16
I'm trying to run a query against a Power BI dataset, but i have issues with the error: The syntax for 'SELECT' is incorrect. I am not getting a clear understanding as to where the error is. Any help is appreciated. 
 
  • Verified answer
    lbendlin Profile Picture
    lbendlin 7,913 on 05 Oct 2024 at 00:04:17
    Running a query against a Power BI dataset / The syntax for 'SELECT' is incorrect
    You need to cut out the entire Direct Query section. Only use 
     
     
    DEFINE
        VAR __DS0FilterTable = 
            FILTER(
                KEEPFILTERS(VALUES('routing_calls'[Date Filter])),
                'routing_calls'[Date Filter] = 1
            )
        VAR __DS0FilterTable2 = 
            FILTER(
                KEEPFILTERS(VALUES('routing_calls'[Program])),
                AND(
                    'routing_calls'[Program] IN {"HARDSURF",
                        "CARPET"},
                    'routing_calls'[Program] IN {"CARPET",
                        "HARDSURF"}
                )
            )
        VAR __DS0FilterTable3 = 
            FILTER(KEEPFILTERS(VALUES('routing_calls'[IFFormula])), 'routing_calls'[IFFormula] = 1)
    EVALUATE 
            CALCULATETABLE(
                SUMMARIZE(
                    'routing_calls',
                    'routing_calls'[Program],
                    'routing_calls'[Store],
                    'routing_calls'[Work Order],
                    'routing_calls'[Billing Type],
                    'routing_calls'[ActivityDateTime],
                    'routing_calls'[Attention],
                    'routing_calls'[PhoneNo],
                    'routing_calls'[Email],
                    'routing_calls'[Routing Call Made],
                    'routing_calls'[Entered By],
                    'routing_calls'[Notes],
                    'routing_calls'[Store Code]
                ),
                KEEPFILTERS(__DS0FilterTable),
                KEEPFILTERS(__DS0FilterTable2),
                KEEPFILTERS(__DS0FilterTable3)
            )

  • mkohler281 Profile Picture
    mkohler281 16 on 04 Oct 2024 at 16:31:33
    Running a query again a Power BI dataset / The syntax for 'SELECT' is incorrect
    Here is the Query Text
    ==========================================
     
    // DAX Query
    DEFINE
        VAR __DS0FilterTable = 
            FILTER(
                KEEPFILTERS(VALUES('routing_calls'[Date Filter])),
                'routing_calls'[Date Filter] = 1
            )
        VAR __DS0FilterTable2 = 
            FILTER(
                KEEPFILTERS(VALUES('routing_calls'[Program])),
                AND(
                    'routing_calls'[Program] IN {"HARDSURF",
                        "CARPET"},
                    'routing_calls'[Program] IN {"CARPET",
                        "HARDSURF"}
                )
            )
        VAR __DS0FilterTable3 = 
            FILTER(KEEPFILTERS(VALUES('routing_calls'[IFFormula])), 'routing_calls'[IFFormula] = 1)
        VAR __DS0Core = 
            CALCULATETABLE(
                SUMMARIZE(
                    'routing_calls',
                    'routing_calls'[Program],
                    'routing_calls'[Store],
                    'routing_calls'[Work Order],
                    'routing_calls'[Billing Type],
                    'routing_calls'[ActivityDateTime],
                    'routing_calls'[Attention],
                    'routing_calls'[PhoneNo],
                    'routing_calls'[Email],
                    'routing_calls'[Routing Call Made],
                    'routing_calls'[Entered By],
                    'routing_calls'[Notes],
                    'routing_calls'[Store Code]
                ),
                KEEPFILTERS(__DS0FilterTable),
                KEEPFILTERS(__DS0FilterTable2),
                KEEPFILTERS(__DS0FilterTable3)
            )
        VAR __DS0PrimaryWindowed = 
            TOPN(
                501,
                __DS0Core,
                'routing_calls'[PhoneNo],
                1,
                'routing_calls'[Program],
                1,
                'routing_calls'[Store],
                1,
                'routing_calls'[Work Order],
                1,
                'routing_calls'[Billing Type],
                1,
                'routing_calls'[ActivityDateTime],
                1,
                'routing_calls'[Attention],
                1,
                'routing_calls'[Email],
                1,
                'routing_calls'[Routing Call Made],
                1,
                'routing_calls'[Entered By],
                1,
                'routing_calls'[Notes],
                1,
                'routing_calls'[Store Code],
                1
            )
    EVALUATE
        __DS0PrimaryWindowed
    ORDER BY
        'routing_calls'[PhoneNo],
        'routing_calls'[Program],
        'routing_calls'[Store],
        'routing_calls'[Work Order],
        'routing_calls'[Billing Type],
        'routing_calls'[ActivityDateTime],
        'routing_calls'[Attention],
        'routing_calls'[Email],
        'routing_calls'[Routing Call Made],
        'routing_calls'[Entered By],
        'routing_calls'[Notes],
        'routing_calls'[Store Code]

    // Direct Query
    SELECT 
    TOP (501) [c1],[c2],[c3],[c4],[c6],[c8],[c9],[c10],[c11],[c12],[c13],[c23]
    FROM 
    (
    SELECT [t0].[ActivityDateTime] AS [c1],[t0].[Attention] AS [c2],[t0].[BillingType] AS [c3],[t0].[Program] AS [c4],[t0].[WoDisp] AS [c6],[t0].[RcallMade] AS [c8],[t0].[Notes] AS [c9],[t0].[CustID] AS [c10],[t0].[PhoneNo] AS [c11],[t0].[Email] AS [c12],[t0].[Store Code] AS [c13],[t0].[Date Filter] AS [c15],[t0].[IFFormula] AS [c20],[t0].[Entered By] AS [c23],[t0].[ActivityDateTime] AS [o5]
    FROM 
    (
    SELECT [c1] AS [ActivityDateTime],[c2] AS [Attention],[c3] AS [BillingType],[c4] AS [Program],[c6] AS [WoDisp],[c8] AS [RcallMade],[c9] AS [Notes],[c10] AS [CustID],[c11] AS [PhoneNo],[c12] AS [Email],[c13] AS [Store Code],[c15] AS [Date Filter],
    (
    CASE
    WHEN 
    (
    (
    NOT(
    (
    [c16] IS NULL 
    )
    )
     AND 
    ([c16] = 5)
    )
     AND 
    (COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c18], CAST(-2 AS datetime)))
    )
    THEN 1
    WHEN 
    (
    (
    NOT(
    (
    [c16] IS NULL 
    )
    )
     AND 
    ([c16] = 4)
    )
     AND 
    (COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c18], CAST(-2 AS datetime)))
    )
    THEN 1
    WHEN 
    (
    (
    NOT(
    (
    [c16] IS NULL 
    )
    )
     AND 
    ([c16] = 3)
    )
     AND 
    (COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c18], CAST(-2 AS datetime)))
    )
    THEN 1
    WHEN 
    (
    (
    NOT(
    (
    [c16] IS NULL 
    )
    )
     AND 
    ([c16] = 2)
    )
     AND 
    (COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c18], CAST(-2 AS datetime)))
    )
    THEN 1
    WHEN 
    (
    (
    NOT(
    (
    [c16] IS NULL 
    )
    )
     AND 
    ([c16] = 1)
    )
     AND 
    (COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c18], CAST(-2 AS datetime)))
    )
    THEN 1
    WHEN 
    (
    (
    NOT(
    (
    [c16] IS NULL 
    )
    )
     AND 
    ([c16] = 6)
    )
     AND 
    (COALESCE([c17], CAST(-2 AS datetime)) >= COALESCE([c18], CAST(-2 AS datetime)))
    )
    THEN 1
    WHEN 
    (
    (
    NOT(
    (
    [c16] IS NULL 
    )
    )
     AND 
    ([c16] = 7)
    )
     AND 
    (COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c18], CAST(-2 AS datetime)))
    )
    THEN 1
    WHEN 
    (
    (
    NOT(
    (
    [c16] IS NULL 
    )
    )
     AND 
    ([c16] = 7)
    )
     AND 
    (COALESCE([c17], CAST(-2 AS datetime)) = COALESCE([c21], CAST(-2 AS datetime)))
    )
    THEN 1
    ELSE 0
    END
    )
     AS [IFFormula],[c23] AS [Entered By]
    FROM 
    (
    SELECT [c1],[c2],[c3],[c4],[c6],[c8],[c9],[c10],[c11],[c12],[c13],
    (
    CASE
    WHEN 
    (
    NOT(
    (
    [c14] IS NULL 
    )
    )
     AND 
    ([c14] = CAST( '20241004 00:00:00' AS datetime))
    )
    THEN 0
    ELSE 1
    END
    )
     AS [c15],DATEPART(WEEKDAY, [c19]) AS [c16],CAST(CAST(
    CAST([c14] AS VARCHAR(4000))
     AS DATE) AS DATETIME) AS [c17],CAST(CAST(
    CAST(
    (SELECT COALESCE(Op1 + Op2, Op1, CAST(Op2 - 2 AS DATETIME)) FROM (SELECT [c19] AS Op1, 1 AS Op2) AS AuxTable)
     AS VARCHAR(4000))
     AS DATE) AS DATETIME) AS [c18],CAST(CAST(
    CAST(
    (SELECT COALESCE(Op1 + Op2, Op1, CAST(Op2 - 2 AS DATETIME)) FROM (SELECT [c19] AS Op1, 2 AS Op2) AS AuxTable)
     AS VARCHAR(4000))
     AS DATE) AS DATETIME) AS [c21],[c23]
    FROM 
    (
    SELECT [t0].[ActivityDateTime] AS [c1],[t0].[Attention] AS [c2],[t0].[BillingType] AS [c3],[t0].[Program] AS [c4],[t0].[WoDisp] AS [c6],[t0].[RcallMade] AS [c8],[t0].[Notes] AS [c9],[t0].[CustID] AS [c10],[t0].[PhoneNo] AS [c11],[t0].[Email] AS [c12],RIGHT([t0].[OfficeLocation], 1) AS [c13],CAST(CAST(
    CAST([t0].[ActivityDateTime] AS VARCHAR(4000))
     AS DATE) AS DATETIME) AS [c14],CAST( '20241004 00:00:00' AS datetime) AS [c19],[t0].[Entered By] AS [c23]
    FROM 
    (
    (
    select [_].[ActivityDateTime] as [ActivityDateTime],
        [_].[Attention] as [Attention],
        [_].[BillingType] as [BillingType],
        [_].[SummaryProgram] as [Program],
        [_].[OfficeLocation] as [OfficeLocation],
        [_].[WoDisp] as [WoDisp],
        [_].[RcallResults] as [RcallResults],
        [_].[RcallMade] as [RcallMade],
        [_].[EnteredDateTime] as [Entered Date],
        [_].[EnteredBy] as [Entered By],
        [_].[Notes] as [Notes],
        [_].[CustID] as [CustID],
        [_].[PhoneNo] as [PhoneNo],
        [_].[Email] as [Email]
    from [dbo].[trav_tblCR2Alert_RoutingCallResults_View] as [_]
    )
    )
     AS [t0]
    )
     AS [t0]
    )
     AS [t0]
    )
     AS [t0]
    )
     AS [t0]
    WHERE 
    (
    (
    (
    [c15] = 1
    )
     AND 
    (
    [c20] = 1
    )
    )
     AND 
    (
    ([c4] IN (N'HARDSURF',N'CARPET'))
    )
    )
    GROUP BY [c1],[c2],[c3],[c4],[c6],[c8],[c9],[c10],[c11],[c12],[c13],[c23],[o5]
    ORDER BY [c11]
    ASC
    ,[c4]
    ASC
    ,[c10]
    ASC
    ,[c6]
    ASC
    ,[c3]
    ASC
    ,
    COALESCE([o5], CAST(-2 AS datetime))
    ASC
    , (CASE WHEN [o5] IS NULL THEN 0 ELSE 1 END)
    ASC
    ,[c2]
    ASC
    ,[c12]
    ASC
    ,[c8]
    ASC
    ,[c23]
    ASC
    ,[c9]
    ASC
    ,[c13]
    ASC
     
     
  • Verified answer
    abc 123 Profile Picture
    abc 123 710 on 04 Oct 2024 at 15:44:46
    Running a query again a Power BI dataset / The syntax for 'SELECT' is incorrect
    What were your entries in the action?
  • Verified answer
    FLMike Profile Picture
    FLMike 29,371 on 04 Oct 2024 at 15:41:46
    Running a query again a Power BI dataset / The syntax for 'SELECT' is incorrect
    HI
     
    We need to see the actual settings you did on the actions. As its clear its a DAX related syntax issue, but no way to help as we need to see what you did in the actions.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #9 Get Recognized…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 144,479

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 64,350

Leaderboard