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; 

[burakkaragoz]

Hi @Kaushik7jp ,

While v-csrikanth and MFelix are correct that pasting the SQL into the connector is the fastest way to get the table, it is often not the best way for DirectQuery. Pasting complex SQL prevents Query Folding, which kills performance, and it makes the report static (you cannot dynamically change that 'MM/DD/YYYY' date using a slicer).

Since you asked for the measure-based approach, here is how you translate SQL logic into Power BI semantic models.

1. The "Left Join" (Query 1)

In Power BI, you do not write code for joins. You rely on the Model.

• SQL Thinking: Write LEFT JOIN EmployeeDetailTable ON ...

• Power BI Thinking: Create a relationship between EmployeeTable (One) and EmployeeDetailTable (Many).

  • Note: If your join key is composite (EmployeeID + CompanyID), you must create a "Composite Key" column in both tables in your data source view (e.g., EmpID-CompanyID) to form the relationship, as Power BI only supports single-column relationships.

Once related, simply drag columns from both tables into a Table Visual. The "Left Join" behavior happens automatically (rows with no details will show blanks).

To replicate the WHERE clause filters:

• JobCode, CompanyID: Drag these columns to the "Filters on this visual" pane and select the values (YY, ZZZ).

• The "OR" Logic (XXX or NULL): This is tricky in filters. Use a Measure for the value to handle the logic:

  Display_Rate = 
  CALCULATE(
      SUM(EmployeeDetailTable[RateAmount]),
      -- Filter: Keep row if Code is XXX or the row is from the 'Left' side (Blank)
      EmployeeDetailTable[DetailCode] = "XXX" || ISBLANK(EmployeeDetailTable[DetailCode])
  )

2. The "Row Number / Top N" (Query 2)

This is the most common SQL-to-DAX challenge. You want the "Latest Record" per employee. In SQL, you use ROW_NUMBER(). In Power BI, you use a measure to flag the latest row.

The Measure: Create this measure and add it to your Table Visual as a Visual Level Filter set to is 1.

Is Latest Record = 
VAR _SelectedDate = MAX(PayrollDetailTable[PeriodEndDate])
VAR _MaxDateForGroup = 
    CALCULATE(
        MAX(PayrollDetailTable[PeriodEndDate]),
        -- Remove filters from Date, keep filters on Employee/DetailCode
        ALLEXCEPT(PayrollDetailTable, PayrollDetailTable[EmployeeID], PayrollDetailTable[DetailCode]),
        -- Replicate the "WHERE Date <= X" logic dynamically
        PayrollDetailTable[PeriodEndDate] <= MAX('DateSlicer'[Date]) 
    )

RETURN 
    IF(_SelectedDate = _MaxDateForGroup, 1, 0)

Why this is better:

• Dynamic: The MAX('DateSlicer'[Date]) part means your users can change the cutoff date using a standard Date Slicer, and the "Latest Record" logic updates instantly. The SQL paste method cannot do this.

• Performance: It allows Power BI to send native queries for the aggregation rather than pulling a giant SQL table first.

Hope this guides you toward the "Power BI way" of thinking!

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[v-csrikanth]

Hi @Kaushik7jp 
 @MFelix Thanks for the clarification shared above.

Using the SQL statement in the Advanced options with DirectQuery addresses the requirement and preserves the original SQL logic. If the goal differs from this approach, additional details on the model setup and expected output would help clarify next steps.

If you still need assistance, we are happy to assist you or any other new issue you're welcome to start a new thread in the community at any time.

We appreciate your understanding and participation.

Thanks and Regards,
Cheri Srikanth
CST Team.

[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