Forum Discussion
Need help converting SQL query into DAX
I have the following SQL query that I would like to convert into DAX but keep running into stumbling blocks
SELECT DISTINCT
InvestorCompanyCode
into #investmentVehicleCodes
FROM
StdParametersNRT AS std
join dbo.fn_split(@investorGroup, ',') as a on std.InvestorGroup = a.value
join dbo.fn_split(@companyGroup, ',') as b on std.CompanyGroup = b.value
--PAYMENT ENTRIES
SELECT
T2_CompanyCode,
DocumentNo_,
sourcename,
T2_SourceCode,
t2_description,
t2_g_laccountno_,
[T2_Amount(CCY)],
T2_PostingDate
FROM
factgl AS gl
JOIN #investmentVehicleCodes AS x ON gl.T2_CompanyCode = x.InvestorCompanyCode
WHERE
T2_SourceCode = 'PAYMENTJNL'
AND T2_G_LAccountNo_ = '20200'
AND T2_PostingDate BETWEEN @startDate AND @asOfDate
AND T2_Description NOT LIKE '%reversal%'
AND EXISTS
(
--EXPENSE ENTRIES
SELECT
NULL
FROM
FactGL AS y
JOIN #investmentVehicleCodes AS x ON gl.T2_CompanyCode = x.InvestorCompanyCode
WHERE
y.T2_Description = gl.T2_Description
AND T2_PostingDate >= DATEADD(YEAR, -1, @asOfDate)
AND y.T2_G_LAccountNo_ IN (SELECT value from dbo.fn_split(@accountNo, ',')) --= @accountNo
AND y.[T2_Amount(CCY)] = gl.[T2_Amount(CCY)]
AND y.T2_Description NOT LIKE '%reversal%'
AND y.T2_SourceCode = 'GENEXP'
AND y.SourceName = gl.SourceName
AND y.T2_CompanyCode = gl.T2_CompanyCode
)
ORDER BY
T2_PostingDate DESC, T2_Description
The part that gives me trouble (I think) is the EXISTS clause. Tried various methods based on Dax.guide site. Below are some of the attempts but no luck.
EVALUATE
var distinctCompanyCodes =
DISTINCT
(
SELECTCOLUMNS
(
FILTER
(
StdParamNRT,
StdParamNRT[CompanyGroup] = "CWT"
),
"InvestorCompanyCode", StdParamNRT[InvestorCompanyCode]
)
)
var payments = CALCULATETABLE(
'PowerBI vFactGL',
'PowerBI vFactGL'[T2_SourceCode] = "PAYMENTJNL" ,
'PowerBI vFactGL'[t2_g_laccountno_] = "20200" ,
'PowerBI vFactGL'[T2_PostingDate] >= datevalue("4/1/2023"),
'PowerBI vFactGL'[T2_PostingDate] <= datevalue("4/30/2023"),
distinctCompanyCodes
)
-- T2_Description
var expenses = CALCULATETABLE(
'PowerBI vFactGL',
'PowerBI vFactGL'[T2_PostingDate] >= datevalue("4/30/2022") ,
'PowerBI vFactGL'[t2_g_laccountno_] = "52300" ,
'PowerBI vFactGL'[T2_SourceCode] = "GENEXP",
distinctCompanyCodes
)
VAR result =
CALCULATETABLE
(
payments,
INTERSECT
(
payments,
expenses
)
)
/*
CALCULATETABLE
(
payments,
TREATAS
(
values ('payments'[T2_Description]),
'expenses'[T2_Description]
)
)
*/
/*
CALCULATETABLE
(
payments,
FILTER
(
CALCULATETABLE
(
'PowerBI vFactGL',
'PowerBI vFactGL'[T2_SourceCode] = "PAYMENTJNL" ,
'PowerBI vFactGL'[t2_g_laccountno_] = "20200" ,
'PowerBI vFactGL'[T2_PostingDate] >= datevalue("4/1/2023"),
'PowerBI vFactGL'[T2_PostingDate] <= datevalue("4/30/2023"),
distinctCompanyCodes
),
contains
(
payments,
'payments'[t2_description],
'PowerBI vFactGL'[t2_description]
)
)
)
*/
return
result
Any help would be most appreciated.
Hello brdrok,
Here's a suggested DAX translation of your SQL code:
EVALUATE VAR distinctCompanyCodes = CALCULATETABLE ( VALUES ( StdParamNRT[InvestorCompanyCode] ), StdParamNRT[CompanyGroup] = "CWT" ) VAR payments = CALCULATETABLE ( 'PowerBI vFactGL', 'PowerBI vFactGL'[T2_SourceCode] = "PAYMENTJNL", 'PowerBI vFactGL'[t2_g_laccountno_] = "20200", 'PowerBI vFactGL'[T2_PostingDate] >= DATEVALUE ( "4/1/2023" ), 'PowerBI vFactGL'[T2_PostingDate] <= DATEVALUE ( "4/30/2023" ), distinctCompanyCodes ) VAR expenses = CALCULATETABLE ( 'PowerBI vFactGL', 'PowerBI vFactGL'[T2_PostingDate] >= DATEVALUE ( "4/30/2022" ), 'PowerBI vFactGL'[t2_g_laccountno_] = "52300", 'PowerBI vFactGL'[T2_SourceCode] = "GENEXP", distinctCompanyCodes ) VAR results = FILTER ( payments, VAR currentDescription = 'PowerBI vFactGL'[T2_Description] VAR currentAmount = 'PowerBI vFactGL'[T2_Amount(CCY)] VAR currentSourceName = 'PowerBI vFactGL'[SourceName] VAR currentCompanyCode = 'PowerBI vFactGL'[T2_CompanyCode] RETURN COUNTROWS ( FILTER ( expenses, 'PowerBI vFactGL'[T2_Description] = currentDescription && 'PowerBI vFactGL'[T2_Amount(CCY)] = currentAmount && 'PowerBI vFactGL'[SourceName] = currentSourceName && 'PowerBI vFactGL'[T2_CompanyCode] = currentCompanyCode ) ) > 0 ) RETURN resultsThe key is in the results variable where for each record in payments, we are checking if a corresponding record exists in expenses that matches on multiple columns. Should you require my further assistance, please do not hesitate to reach out to me.
2 Replies
- Sahir_MaharajSuper User
Hello brdrok,
Here's a suggested DAX translation of your SQL code:
EVALUATE VAR distinctCompanyCodes = CALCULATETABLE ( VALUES ( StdParamNRT[InvestorCompanyCode] ), StdParamNRT[CompanyGroup] = "CWT" ) VAR payments = CALCULATETABLE ( 'PowerBI vFactGL', 'PowerBI vFactGL'[T2_SourceCode] = "PAYMENTJNL", 'PowerBI vFactGL'[t2_g_laccountno_] = "20200", 'PowerBI vFactGL'[T2_PostingDate] >= DATEVALUE ( "4/1/2023" ), 'PowerBI vFactGL'[T2_PostingDate] <= DATEVALUE ( "4/30/2023" ), distinctCompanyCodes ) VAR expenses = CALCULATETABLE ( 'PowerBI vFactGL', 'PowerBI vFactGL'[T2_PostingDate] >= DATEVALUE ( "4/30/2022" ), 'PowerBI vFactGL'[t2_g_laccountno_] = "52300", 'PowerBI vFactGL'[T2_SourceCode] = "GENEXP", distinctCompanyCodes ) VAR results = FILTER ( payments, VAR currentDescription = 'PowerBI vFactGL'[T2_Description] VAR currentAmount = 'PowerBI vFactGL'[T2_Amount(CCY)] VAR currentSourceName = 'PowerBI vFactGL'[SourceName] VAR currentCompanyCode = 'PowerBI vFactGL'[T2_CompanyCode] RETURN COUNTROWS ( FILTER ( expenses, 'PowerBI vFactGL'[T2_Description] = currentDescription && 'PowerBI vFactGL'[T2_Amount(CCY)] = currentAmount && 'PowerBI vFactGL'[SourceName] = currentSourceName && 'PowerBI vFactGL'[T2_CompanyCode] = currentCompanyCode ) ) > 0 ) RETURN resultsThe key is in the results variable where for each record in payments, we are checking if a corresponding record exists in expenses that matches on multiple columns. Should you require my further assistance, please do not hesitate to reach out to me.
- brdrokHelper I
Sahir_Maharaj . Oh my goodness...this is just pure awesomeness. A million thank you!!!!