Complex RLS

[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)

[v-chuncz-msft]

@Watty,

DAX provides five functions to manage data that is presented as a parent-child hierarchy in models. You could take a look at it.

[Watty]

Hi Sam

Thanks for the reply.

When looking at this it looks like these are for reading a parent child releationship.

I am looking to restrict the rows processed by power bi in a similar way that [column]=usernam() adds on to the where clause