Re: Complex SQL to DAX

jeronimo2334 · ‎08-04-2019

I have a SQL query that does aggregations between tables that join on multiple columns.

When I try to import this query the server times out, so I imported the tables hoping I could use an equivalent DAX query.

Thanks in advance.

SELECT
	d.description AS 'description',
	d.id_number AS 'ID Number',
	d.accnt_type AS 'Account Type',
	d.dbtpaytype AS 'Paycode',
	d.first_name AS 'First',
	d.last_name AS 'Last',
	s.payment,
	s.Charges,
	t.payment,
	t.Payments,
	( COALESCE ( t.Payments, 0 ) - COALESCE ( s.Charges, 0 ) ) AS Balance 
	FROM
		StAccount d
		LEFT JOIN (
		SELECT
			school,
			id_number,
			payment,
			SUM( amount ) AS Charges 
		FROM
			Transact
		GROUP BY
			id_number,
			payment,
			school 
		) s ON ( s.id_number = d.id_number AND s.school = d.school AND s.payment = d.dbtpaytype )
		LEFT JOIN (
		SELECT
			school,
			id_number,
			payment,
			SUM( amount ) AS Payments 
		FROM
			Transfer
		WHERE
			type = 'Dep' 
		GROUP BY
			id_number,
			payment,
			school 
		) t ON ( t.id_number = d.id_number AND t.school = d.school AND t.payment = d.dbtpaytype ) 
	WHERE
		 d.debit = 1 
	GROUP BY
		d.id_number