Forum Discussion

brdrok's avatar
brdrok
Helper I
2 years ago
Solved

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

2 Replies

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