Forum Discussion

k_h_s's avatar
k_h_s
Frequent Visitor
7 months ago
Solved

Issue with left join using measures

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 upgrade...
Need Help
Show and Tell
  • sarahlns's avatar
    sarahlns
    7 months ago

    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.

alish_b's avatar
alish_b
Super User
7 months ago

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!