Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
brdrok
Frequent Visitor

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.

 

1 ACCEPTED SOLUTION
Sahir_Maharaj
Super User
Super 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
    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.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

2 REPLIES 2
brdrok
Frequent Visitor

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

Sahir_Maharaj
Super User
Super 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
    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.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.