Multi condition DAX row filter

Question

I am trying to convert the following SQL row level security function into a DAX filter within a Tabular model

CREATE FUNCTION [Security].[fn_securitypredicate](@BrandID AS INT, @ChannelId AS INT)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN 
     (SELECT 1 AS fn_securitypredicate
            WHERE (EXISTS ( SELECT 1 FROM   security.RLSStaffBrand  WHERE StaffUsername = SYSTEM_USER AND BrandId = @BrandID)  
              AND  EXISTS ( SELECT 1 FROM   security.RLSStaffChannel WHERE StaffUsername = SYSTEM_USER  AND ChannelId = @ChannelID) )

               OR (        EXISTS (SELECT 1  FROM   security.RLSStaffBrand  WHERE StaffUsername = SYSTEM_USER  AND BrandId = @BrandID)  
                   AND NOT EXISTS ( SELECT 1  FROM   security.RLSStaffChannel WHERE StaffUsername = SYSTEM_USER ) )-- this user is not restricted by Channel

                OR (NOT EXISTS ( SELECT 1  FROM   security.RLSStaffBrand WHERE StaffUsername = SYSTEM_USER)          
                    AND EXISTS ( SELECT 1  FROM   security.RLSStaffChannel 

WHERE StaffUsername = SYSTEM_USER  AND ChannelId = @ChannelID) ) 

)
    GO

So far I have the following DAX filters, but this only handles the first condition in the SQL code. I don't know if it's even possible to replicate the rest in DAX.

='Brand'[BrandId]=LOOKUPVALUE('RLSStaffBrand'[BrandId], 'RLSStaffBrand'[StaffUsername], USERNAME(), 'RLSStaffBrand'[BrandId], 'Brand'[BrandId])

='Channel'[ChannelId]=LOOKUPVALUE('RLSStaffChannel'[ChannelId], 'RLSStaffChannel'[StaffUsername], USERNAME(), 'RLSStaffChannel'[ChannelId], 'Channel'[ChannelId]) 

Answer 1

Not an answer to your exact question, but instead of translating the SQL to DAX and maintaining it in two places, you can reuse the SQL logic in your RLS implementation to materialize tables in your tabular model with the allowed (Username,BrandId) pairs, and the allowed (Username,ChannelId) pairs enumerated.

So write a view and load a table in your tabular model with the results of a query like this:

select s.BrandId,c.ChannelId,u.StaffUserName
from dbo.Brands b
cross join dbo.Channels c
cross join dbo.Staff s
cross apply   [Security].[fn_securitypredicate](s.BrandId,c.ChannelId,s.StaffUserName) sp
where sp.fn_securitypredicate = 1

Which will enumerate all the permissible combinations of (BrandId, ChannelId, StaffUserName). Then use that new table in your DAX row filters by setting up bidirectional cross filtering between this table and Brand and this table and Channel.

David