EXCEPT function not working as expected

[Anonymous]

Hi,

I am trying to show a table with Corporate Units and Locations to which the user does not have access to, called Access. Corporate Units and Locations are dimensions that have DLRS (Dynamic row level security enabled on them).

I am using the except function like this:

Access = except(AllCorpLocation, CorpLocation)

The AllCorpLocation table is an import table that is a query from SQL Server containing all possible Corporate Units and Locations. The second argument is a query (also Import) that is dependant (has a relationship) on a view called vw_Dashboard_Permissions (which is imported), which contains user permissions for using DRLS. It has the Corporate Units and Locations that the current user (through their UPN), has access to.

This is the AllCorpLocation table :

This is the CorpLocation table:

I cannot understand why my Access table returns nothing for a user which does not have access to all Corporate Units and Locations, what I expected to see was to see the Corporate Units and Locations that this user has no access to. (as part of the except function logic).

Any thoughts as to why this might be?

Thank you

[Anonymous]

Hi @Anonymous ,

EXCEPT function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Reference: https://docs.microsoft.com/en-us/dax/except-function-dax#remarks

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Stephen,

Thank you for your answer. What alternative could I use to get the desired result?

[Anonymous · Accepted solution]

Hi @Anonymous ,

After my continuous testing, due to the DAX restrictions in DirectQuery, I did not find a replacement function. It is recommended that you replace the connection mode with the import mode.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.