Need help converting SQL query into DAX

[brdrok]

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.

[brdrok]

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

[Sahir_Maharaj · Accepted solution]

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.