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
