Using SQL declared variables into Power BI

Hi cheid_4838 

I have tried to reproduce your scenarios on my local machine and got an error related to an ORDER BY clause. So, I asked some AI tools to rewrite the query suitable for Power BI, and they gave me this query. It looks like they just removed the ORDER BY clause, but anyway, I have copied the full code here.

SELECT 
    -- Order Information
    ih.ord_hdrnumber AS OrderNum,
    ih.ivh_deliverydate AS Delivery,
    
    -- Origin Information
    orig.cty_name AS Origin,
    orig.cty_state AS OSt,
    orig.cty_region2 AS OReg,
    
    -- Destination Information (with split flag logic)
    ISNULL(
        CASE 
            WHEN l.lgh_split_flag = 'N' 
            THEN dest.cty_name 
            ELSE legdest.cty_name 
        END, 
        'Temple'
    ) AS Dest,
    
    ISNULL(
        CASE 
            WHEN l.lgh_split_flag = 'N' 
            THEN dest.cty_state 
            ELSE legdest.cty_state 
        END, 
        'TX'
    ) AS DSt,
    
    ISNULL(
        CASE 
            WHEN l.lgh_split_flag = 'N' 
            THEN dest.cty_region2 
            ELSE legdest.cty_region2 
        END, 
        'TX-TEM'
    ) AS DReg,
    
    -- Split Flag
    l.lgh_split_flag AS SplitFlag,
    
    -- MT Miles Original (capped at 200)
    CASE 
        WHEN ISNULL(
            (SELECT SUM(ISNULL(stp_lgh_mileage, 0)) 
             FROM stops s1 (NOLOCK) 
             WHERE ih.ord_hdrnumber = s1.ord_hdrnumber 
               AND s1.stp_loadstatus IN ('MT', 'BT')), 0
        ) > 200 
        THEN 200 
        ELSE ISNULL(
            (SELECT SUM(ISNULL(stp_lgh_mileage, 0)) 
             FROM stops s1 (NOLOCK) 
             WHERE ih.ord_hdrnumber = s1.ord_hdrnumber 
               AND s1.stp_loadstatus IN ('MT', 'BT')), 0
        )
    END AS MTMilesOG,
    
    -- MT Miles (capped at 100)
    CASE 
        WHEN ISNULL(
            (SELECT SUM(ISNULL(stp_lgh_mileage, 0)) 
             FROM stops s1 (NOLOCK) 
             WHERE ih.ord_hdrnumber = s1.ord_hdrnumber 
               AND s1.stp_loadstatus IN ('MT', 'BT')), 0
        ) > 100 
        THEN 100 
        ELSE ISNULL(
            (SELECT SUM(ISNULL(stp_lgh_mileage, 0)) 
             FROM stops s1 (NOLOCK) 
             WHERE ih.ord_hdrnumber = s1.ord_hdrnumber 
               AND s1.stp_loadstatus IN ('MT', 'BT')), 0
        )
    END AS MTMiles,
    
    -- Rate from STATEM invoices
    (SELECT TOP 1 ih2.ivh_rate 
     FROM invoiceheader ih2 (NOLOCK) 
     WHERE ih2.ivh_billto = 'STATEM' 
       AND ih2.ivh_deliverydate >= '08-01-2025' 
       AND ih2.ivh_deliverydate < '08-20-2025' 
       AND ih2.ivh_rate > 1.2 
       AND ih2.ivh_rate < 2.5 
     ORDER BY CAST(ih2.ivh_deliverydate AS DATE) DESC
    ) AS Rate,
    
    -- Billing MT Miles calculation
    CASE 
        WHEN ISNULL(
            (SELECT SUM(ISNULL(stp_lgh_mileage, 0)) 
             FROM stops s1 (NOLOCK) 
             WHERE ih.ord_hdrnumber = s1.ord_hdrnumber 
               AND s1.stp_loadstatus IN ('MT', 'BT')), 0
        ) > 100 
        THEN 100 
        ELSE ISNULL(
            (SELECT SUM(ISNULL(stp_lgh_mileage, 0)) 
             FROM stops s1 (NOLOCK) 
             WHERE ih.ord_hdrnumber = s1.ord_hdrnumber 
               AND s1.stp_loadstatus IN ('MT', 'BT')), 0
        )
    END * 
    (SELECT TOP 1 ih2.ivh_rate 
     FROM invoiceheader ih2 (NOLOCK) 
     WHERE ih2.ivh_billto = 'STATEM' 
       AND ih2.ivh_deliverydate >= '08-01-2025' 
       AND ih2.ivh_deliverydate < '08-20-2025' 
       AND ih2.ivh_rate > 1.2 
       AND ih2.ivh_rate < 2.5
    ) AS BillMTMi,
    
    -- FSC Rate
    (SELECT AVG(id.ivd_rate) 
     FROM invoicedetail id (NOLOCK) 
     INNER JOIN invoiceheader ih3 (NOLOCK) ON ih3.ivh_hdrnumber = id.ivh_hdrnumber 
     WHERE id.cht_itemcode LIKE 'FSCMI%' 
       AND ih3.ivh_billto = 'STATEM' 
       AND ih3.ivh_deliverydate >= '08-01-2025' 
       AND ih3.ivh_deliverydate < '08-20-2025'
    ) AS FSCRate,
    
    -- Billing MT FSC calculation
    CASE 
        WHEN ISNULL(
            (SELECT SUM(ISNULL(stp_lgh_mileage, 0)) 
             FROM stops s1 (NOLOCK) 
             WHERE ih.ord_hdrnumber = s1.ord_hdrnumber 
               AND s1.stp_loadstatus IN ('MT', 'BT')), 0
        ) > 100 
        THEN 100 
        ELSE ISNULL(
            (SELECT SUM(ISNULL(stp_lgh_mileage, 0)) 
             FROM stops s1 (NOLOCK) 
             WHERE ih.ord_hdrnumber = s1.ord_hdrnumber 
               AND s1.stp_loadstatus IN ('MT', 'BT')), 0
        )
    END * 
    (SELECT AVG(id.ivd_rate) 
     FROM invoicedetail id (NOLOCK) 
     INNER JOIN invoiceheader ih3 (NOLOCK) ON ih3.ivh_hdrnumber = id.ivh_hdrnumber 
     WHERE id.cht_itemcode LIKE 'FSCMI%' 
       AND ih3.ivh_billto = 'STATEM' 
       AND ih3.ivh_deliverydate >= '08-01-2025' 
       AND ih3.ivh_deliverydate < '08-20-2025'
    ) AS BillMTFSC,
    
    -- Billing Information
    ih.ivh_totalcharge AS BHGrossAmt,
    
    -- Stop Information
    (SELECT MAX(stp_mfh_sequence) 
     FROM stops s (NOLOCK) 
     WHERE s.mov_number = ih.mov_number
    ) AS LastStopID,
    
    ISNULL(s.stp_lgh_mileage, 25) AS LastStopMiles,
    
    -- Revenue Calculations
    0.75 AS RPM,
    0.75 * s.stp_lgh_mileage AS ReduceBHAmount,
    
    CASE 
        WHEN orig.cty_name = 'Brownwood' 
         AND dest.cty_region2 = 'TX-DAL' 
        THEN ih.ivh_totalcharge 
        ELSE ih.ivh_totalcharge - (0.75 * ISNULL(s.stp_lgh_mileage, 25)) 
    END AS BHTotal,
    
    CASE 
        WHEN orig.cty_name = 'Brownwood' 
         AND dest.cty_region2 = 'TX-DAL' 
        THEN ih.ivh_totalcharge * -1 
        ELSE ((ih.ivh_totalcharge - (0.75 * ISNULL(s.stp_lgh_mileage, 25))) * 0.8) * -1 
    END AS BHCredit,
    
    ih.ivh_billto

FROM invoiceheader ih (NOLOCK)
    
    -- Join with origin city
    INNER JOIN city orig (NOLOCK) 
        ON orig.cty_code = ih.ivh_origincity
    
    -- Join with destination city
    INNER JOIN city dest (NOLOCK) 
        ON dest.cty_code = ih.ivh_destcity
    
    -- Optional join with leg header
    LEFT JOIN legheader l (NOLOCK) 
        ON l.ord_hdrnumber = ih.ord_hdrnumber
    
    -- Optional join with leg destination
    LEFT JOIN city legdest (NOLOCK) 
        ON legdest.cty_code = l.lgh_endcity
    
    -- Optional join with last stop
    LEFT JOIN stops s (NOLOCK) 
        ON s.mov_number = ih.mov_number 
        AND s.stp_mfh_sequence = (
            SELECT MAX(stp_mfh_sequence) 
            FROM stops s (NOLOCK) 
            WHERE s.mov_number = ih.mov_number
        )

WHERE 
    -- Filter conditions
    ih.ivh_revtype3 = 'SC-BH'
    AND ih.ivh_billto NOT LIKE 'STATEM%'
    AND ih.ivh_deliverydate >= '08-01-2025' 
    AND ih.ivh_deliverydate < '08-20-2025'
    AND (l.lgh_split_flag IN ('N', 'S') OR l.lgh_split_flag IS NULL)
    AND ih.ivh_invoicenumber = (
        SELECT MAX(ih2.ivh_invoicenumber) 
        FROM invoiceheader ih2 (NOLOCK) 
        WHERE ih2.ord_hdrnumber = ih.ord_hdrnumber 
          AND RIGHT(ivh_invoicenumber, 1) <> 'B'
    )

-- ORDER BY MTMilesOG DESC -- Removed for Power BI compatibility

It looks like just removing the ORDER BY clause makes DirectQuery mode work.