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.
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.