Power bi embedded rls complex

[Watty]

Hi All

I have watched the following video and got this working great,

Summarising what I think is happening, Power BI is smart enough to know the schema and add on the extra where clause using the dax filter [column_name]=username ( where the username is passed in from the token )

Now our requirement for the retriction of data is more complex (sql below) is this possible ?

Help would really be appeciated for this.

https://channel9.msdn.com/Blogs/Seth-Juarez/Power-BI-Embedded-with-Josh-Caplan

;WITH RelationsCTE (ParentId, Id)
AS
(
SELECT
nodes_assigned_to_user.ParentId
,nodes_assigned_to_user.Id
FROM Client.Node AS nodes_assigned_to_user
WHERE
ISNULL(Root_Id, Id) = @ClientId
AND Id IN (
SELECT nur.Node_Id
FROM account.NodeUserRole nur
INNER JOIN account.UserRole ur ON nur.UserRole_Id = ur.Id
WHERE ur.[User_Id] = @UserId
)
UNION ALL
SELECT child_nodes.ParentId, child_nodes.Id
FROM Client.Node AS child_nodes
INNER JOIN RelationsCTE AS related_nodes
ON child_nodes.ParentId = related_nodes.Id
)

SELECT *
FROM client.Node
WHERE Node.Id IN (SELECT Id FROM RelationsCTE)

[Eric_Zhang]

@Watty

Are you in DirectQuery mode?

Based on my test, in DirectQuery, neither Stored Procedure nor CTE is supported, even when I tried to bypass the limitation via wrapping the recursive CTE in a table valued funtion, it still pops up a window saying not supported. So you can just leave the dataset as simple, then try to deal with hierarchy via DAX.