Issue with left join using measures

[k_h_s]

I have build permission on dataset, hence I can not create calculated tables or columns on the model.  I need to join two tables using left join to create the output. My permission can not be upgraded due to RLS. Please help me with a solution. Alternatively, I can not use change anything in advanced query also, as I do not have anything beyond build permission. 

I need to replicate 

Basically I want to replicate 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; in power bi

[v-hjannapu]

Hi @k_h_s,

I would also take a moment to thank @sarahlns  , @alish_b  for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hi @k_h_s,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support Team.

[Praful_Potphode]

Hi @k_h_s ,
As suggested by others, you can create calculated table using DAX functions.but it will not give you the expected performance.the report may not work properly.the best solution  to this is to load tables using SQL and create a composite model.

References:

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models 

Thanks and Regards,

Praful

[sarahlns]

HI k_h_s

Preview

With Build permission only, the only viable option is using virtual tables inside measures.

By combining TOPN (ROW_NUMBER), NATURALLEFTOUTERJOIN, and TREATAS, it is possible to fully replicate complex SQL joins and window functions without modifying the semantic model or Power Query, and while respecting RLS.

[sarahlns]

With Build permission only, the only viable option is using virtual tables inside measures.
By combining TOPN (ROW_NUMBER), NATURALLEFTOUTERJOIN, and TREATAS, it is possible to fully replicate complex SQL joins and window functions without modifying the semantic model or Power Query, and while respecting RLS.

[rajendraongole1]

Hi @k_h_s  - With Build permission only, the only possible solution is a visual-level DAX simulation using measures and virtual tables.You cannot create a real joined dataset.You can reproduce the results inside a visual.

Hope this information helps. let me know if any 

[alish_b]

Hey @k_h_s ,

I would suggest that you keep complex queries in the SQL layer itself as a view or a materialized view and then pull it in the Power Query. While a lot of things are doable in Power Query but sometimes it is just not worth the trouble. You could simplify the logic in SQL view and maybe leave the filtering to Power BI but window functions, sub queries, and joins are better left at SQL layer.
However, if that is not a possibility at all, then please share a sample of the tables and data that you have available in Power BI layer and the expected output and we could better assess the situation at hand and advise accordingly.

Hope it helps!