topic Re: Need help converting SQL query into DAX in DAX Commands and Tips

Need help converting SQL query into DAX

brdrok — Fri, 13 Oct 2023 21:30:12 GMT

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.

Re: Need help converting SQL query into DAX

Sahir_Maharaj — Sun, 15 Oct 2023 12:32:23 GMT

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 results

The 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.

Re: Need help converting SQL query into DAX

brdrok — Mon, 16 Oct 2023 13:41:02 GMT

Sahir_Maharaj . Oh my goodness...this is just pure awesomeness. A million thank you!!!!