Replicating SQL query in Power BI

[Kaushik7jp]

Basically I want to replicate in power bi.

Since calculated tables are not supported in DirectQuery,
what is the best measure-based approach in Power BI to replicate this logic?

Any guidance or best practices would be appreciated.

Thanks in advance.

SELECT
emp.LastName,
emp.FirstName,
emp.JobCode,
emp.JobClass,
emp.CompanyID,
emp.EmployeeID,
det.DetailCode,
det.RateAmount,
det.LimitAmount,
det.VendorID
FROM EmployeeTable AS emp
LEFT OUTER JOIN EmployeeDetailTable AS det
ON emp.EmployeeID = det.EmployeeID
AND emp.CompanyID = det.CompanyID
WHERE
(det.DetailCode = 'XXX' OR det.DetailCode IS NULL)
AND emp.CompanyID = 'YY'
AND emp.IsActive = 'Y'
AND emp.JobCode LIKE '%ZZZ%'
ORDER BY
emp.JobCode ASC,
emp.JobClass ASC;   and SELECT CompanyID, PeriodEndDate, EmployeeID, DetailCode
FROM (
SELECT
CompanyID,
PeriodEndDate,
EmployeeID,
DetailCode,
ROW_NUMBER() OVER (
PARTITION BY EmployeeID, DetailCode
ORDER BY PeriodEndDate DESC
) AS ROW_NUMBER
FROM PayrollDetailTable
WHERE
PeriodEndDate <= 'MM/DD/YYYY'
AND Amount > 0
AND DetailCode BETWEEN 'AAAA' AND 'BBBB'
AND EmployeeID IN (
/* masked list */
)
) t
WHERE t.ROW_NUMBER = 1
ORDER BY EmployeeID ASC, DetailCode ASC; 

[MFelix]

Hi @Kaushik7jp ,

If you want to create this table in Power BI using direct query you can use this exact SQL statement you only need to use the advance options on the SQL connection and past your SQL code on the window that show for SQL query:

If this is not what you need can you please elaborate better on what you want to achieve and how your model is setup, since there is no direct syntax between SQL and DAX queries