SQL to DAX conversion

[psilos24]

Hello Experts,

I am facing issue while converting below SQL query into DAX. Can you please help me to convert the query:

SELECT
first_name AS 'Name',
last_name AS 'Surname',
IFNULL(l.logged_hours, 0.0) AS 'Logged Hours',
IFNULL(b.billable_hours, 0.0) AS 'Billable Hours',
IFNULL(nb.non_billable_hours, 0.0) AS 'Non-Billable Hours'
FROM
at_user u
LEFT JOIN
(SELECT user_id, sum(actuals) / 60 as logged_hours
FROM tt_record
GROUP BY user_id) AS l ON u.id = l.user_id
LEFT JOIN
(SELECT user_id, sum(actuals) / 60 as billable_hours
FROM
(SELECT
user_id, task_id, actuals, billing_type_id
FROM
actitimedb.tt_record r LEFT JOIN task t ON r.task_id = t.id LEFT JOIN project p ON p.id = t.project_id
WHERE
p.customer_id <> 383) ch
GROUP BY
user_id) AS b ON b.user_id = u.id
LEFT JOIN
(SELECT user_id, sum(actuals) / 60 as non_billable_hours
FROM
(SELECT
user_id, task_id, actuals, billing_type_id
FROM
actitimedb.tt_record r LEFT JOIN task t ON r.task_id = t.id LEFT JOIN project p ON p.id = t.project_id
WHERE
p.customer_id = 383) ch
GROUP BY
user_id) AS nb ON nb.user_id = u.id
WHERE
u.username NOT IN ('.....',
'.....',
'....',
'....',
'....',
'....',
'...',
'.....' )
ORDER BY Surname, Name

[v-frfei-msft]

Hi  @psilos24 ,

Please refer to this article.

https://www.sqlbi.com/articles/from-sql-to-dax-projection/

Also you can share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[HI_IM_THIBBY]

Hello psilos24,

What exactly are you wanting to do here? Create a measure? A summary table? An entire table in your model? I see you're pulling in 5 fields so I'd assume you're wanting this to be a table in your model.