Complex SQL to DAX

[jeronimo2334]

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 

[v-xuding-msft]

Hi @jeronimo2334 ,

For joining tables, there is a blog you can reference to change SQL to DAX.

Best Regards,

Xue

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jeronimo2334]

@Anonymous 

As far as I know Power Query can not handle multiple joins.

[Anonymous]

@jeronimo2334 

I am not aware of your data size however you can try this.

https://www.dropbox.com/s/3bbqthegu6f6uzv/Complex%20SQL%20to%20DAX.pbix?dl=0

On heavy data this solution might not work but you give a try 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Proud to be Datanaut!

[Anonymous]

@jeronimo2334 

You can do the same steps in Power Query instead.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.