Multi condition DAX row filter

by Adrian Sugden   Last Updated September 13, 2017 14:06 PM

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


Related Questions






How make an SSAS Tabular model case sensitive?

Updated August 10, 2017 10:06 AM