I have a query that was written in SSRS that I would like to use in Power BI. The query main query is in bold below. The query references query #1 (@csr) and #2 (@Status) shown below the main query. Is there an easy way to write this in Power BI. I have tried a couple of different ways using VAR and can't get it to work. Is there another method I am not thinking of? Thanks.
--DECLARE @StartDate AS DATETIME DECLARE @EndDate AS DATETIME SET @StartDate = '5/1/2023' SET @EndDate = '5/31/2023'
--DECLARE @BillTo as varchar(max) SET @BillTo = '%'
SELECT
o.ord_billto as BillTo
, o.ord_hdrnumber as OrderNum
, o.ord_startdate as Pickup
, o.ord_completiondate as Delivery
, o.ord_status as Status
, o.ord_shipper as Shipper
, c.cty_name as Origin
, c.cty_state as OState
, o.ord_consignee as Consignee
, c2.cty_name as Destination
, c2.cty_state as DState
, o.ord_totalcharge as TotalRev
, o.ord_driver1 as Driver
, o.ord_tractor as Tractor
, o.ord_trailer as Trailer
, o.ord_bookedby as BookedBy
, case when co.cmp_othertype2 = '' then 'UNK' else isnull(co.cmp_othertype2,'UNK') end as CSRID
, isnull(lf.name,'UNKNOWN') as CSR
, lf2.name as DrvMgr
, m.mpp_teamleader
FROM orderheader o (NOLOCK)
INNER JOIN city c (NOLOCK) ON c.cty_code = o.ord_origincity
INNER JOIN city c2 (NOLOCK) ON c2.cty_code = o.ord_destcity
INNER JOIN company co (NOLOCK) ON co.cmp_id = o.ord_billto
LEFT JOIN labelfile lf (NOLOCK) ON lf.abbr = co.cmp_othertype2 and lf.labeldefinition = 'othertypes2'
LEFT JOIN manpowerprofile m (NOLOCK) ON m.mpp_id = o.ord_driver1
LEFT JOIN labelfile lf2 (NOLOCK) ON lf2.abbr = m.mpp_teamleader and lf2.labeldefinition = 'teamleader'
WHERE o.ord_completiondate >= @StartDate and o.ord_completiondate < @EndDate+1
AND o.ord_billto LIKE @BillTo + '%'
AND o.ord_status NOT IN ('CAN','MST')
AND case when co.cmp_othertype2 = '' then 'UNK' else isnull(co.cmp_othertype2,'UNK') end in (@CSR)
AND o.ord_status IN (@Status)
AND m.mpp_teamleader in (@DM)
LookUp Queries
#1 (@csr)
SELECT
abbr
, name
FROM labelfile (nolock)
WHERE labeldefinition = 'othertypes2'
AND retired = 'N'
UNION ALL
SELECT
abbr
, name
FROM labelfile (nolock)
WHERE labeldefinition = 'othertypes2'
AND abbr = 'UNK'
order by name
#2 (@Status)
SELECT DISTINCT
ord_status
FROM orderheader (NOLOCK)
WHERE ord_completiondate > getdate()-60
AND ord_status NOT IN ('CAN','MST')
ORDER BY ord_status
