0

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.

Ivan Kaloyanov
  • 1,748
  • 6
  • 18
  • 24
ivric
  • 109
  • 1
  • 6
  • 24
  • I've tried this =CALCULATETABLE(FILTER('Projects',Projects[Special_Project]=FALSE())) but didn't work – ivric Jan 02 '20 at 21:48
  • 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 https://ozh.github.io/ascii-tables/to generate ASCII code) and a clear expected solution. – Seymour Jan 03 '20 at 13:15
  • 1
    @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 Jan 04 '20 at 20:19
  • 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 Jan 04 '20 at 20:29

1 Answers1

3

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.

GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • Thank you for your answer, I'm wondering if this can also be made dynamic by leveraging another table named [Users] which has the Domain\usernames column, and joining [Projects] to [Users]. And instead of creating a new Role - 'PMs with access to Special projects', can a new DAX filter be done to dynamically Allow or Deny access on the existing Role, based on Special_Project column ? – ivric Jan 08 '20 at 16:11
  • @ivric definitely that’s possible. https://learn.microsoft.com/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular – GregGalloway Jan 08 '20 at 16:40
  • @ivric to be clear, membership in a role is based upon the users or groups that are role members. But the row level security filter can by dynamic in that it can use the USERNAME() function to lookup the current user in a table and control which rows that user should see. – GregGalloway Jan 08 '20 at 16:42
  • I've followed a similar approach, I've set the DAX fiter as below but getting error: _The expression refers to multiple columns. Multiple columns cannot be converted to scalar value_ . My DAX is **=IF(CONTAINS('vwUser',vwUser[Login],USERNAME()) ,FILTER(Projects,Projects[Special_Project]=TRUE()) ,FILTER(Projects,Projects[Special_Project]=FALSE()) )** – ivric Jan 10 '20 at 22:31
  • @ivric your espression returns a table. It should return a Boolean. See my revised answer. – GregGalloway Jan 10 '20 at 22:40
  • Thanks @GregGalloway now the error is gone but data doesn't seem to be filtering. I've checked the underlying database and this Special_Project column is a 'Bit' datatype and SSAS reads it as Boolean (True/False) not sure if this is the problem ? – ivric Jan 23 '20 at 13:56
  • What do you mean it isn’t filtering? How did you test? I would build a DAX measure to return the USERNAME() function and make sure that matches your Login column. – GregGalloway Jan 23 '20 at 22:44
  • 1
    I tweaked it a bit and it seem to be working now. =[Special_Project]=IF(CONTAINS('vwuser',vwuser[Login],USERNAME(), TRUE(), FALSE()) – ivric Jan 24 '20 at 00:05