SSAS RLS implementation with DAX

0
votes

I have a SSAS Tabular cube with one of the tables named Projects, which has columns such as Project_Managers, Special_Projects among other columns.

Special_Projects is a Boolean column having values 1 or 0 which states if the project is marked as special or not.

I am implementing basic RLS in this SSAS tabular cube, I have created a Role - 'PMs with access to Special projects'. Now I'm trying to use DAX to select only those rows from the Projects table where Special_Projects=1

I have tried a few DAX formulas like CALCULATETABLE() and EVALUATE() but they did not work.

1
ssasdaxssas-tabularrow-level-security
I've tried this =CALCULATETABLE(FILTER('Projects',Projects[Special_Project]=FALSE())) but didn't work – ivric
In general, please keep your question as simple as possible. It is not relevant to know the name of each table in your model or the data type of each column. Instead please provide a clear table (using this ozh.github.io/ascii-tables/to generate ASCII code) and a clear expected solution. – Seymour
@Seymour with respect, column and table names and data types are important to answer the question. If a table has a space in it you have to put single quotes around it. If a column is Boolean vs. integer then it changes the code. I personally applaud an excellent question including all information needed to provide the code for an answer. – GregGalloway
My point was to provide (1) a simple and clear example with only relevant information needed and (2) the expected result. For example, relevant information needed is the cardinality of the table, which is relevant because it determines the algorithm executed by the engine to apply RLS. – Seymour

1 Answers

3
votes

The row level security filter on the Projects table should be:

=Projects[Special_Project]

These filters should return a Boolean value and rows where the expression is true are visible to the user.

The above assumes the column is of type Boolean like you said. If it’s is 1 or 0 then try:

=Projects[Special_Project]=1

If you need to check another table and also filter on a column in the current table then try:

=IF(CONTAINS('vwUser',vwUser[Login],USERNAME()) ,Projects[Special_Project]=TRUE() ,Projects[Special_Project]=FALSE())

That should show special projects to users in the vwUser table and other projects to users not in that table.