Userrelationship and Role Level Security Issue

sarafparam · ‎02-07-2018

Hi All,

I have already read a couple of blogs on this issue and am still unable to resolve this issue completely

To give the overview of my problem. I have the following model:

DimName and DimSurname are the filters on my dashboard. I have 2 types of measures

1. Measures that userelationship with NameA->Name and SurnameA->Surname, and

2. Measures that userelationship with NameB->Name and SurnameB->Surname

In addition I have role level security applied to the DimName, DimSurname and Fact

If I use "Userelationship" I get error which is a known limitation of tabular model (Chriss Webb's blogpost: https://blog.crossjoin.co.uk/2013/05/10/userelationship-and-tabular-row-security/)

I tried to use Virtual Relationship using FILTER ALL and CONTAINS ( Marco's post https://www.daxpatterns.com/handling-different-granularities/ )

This works fine but it fails to override the active relationship: For the measure below for eg:

CALCULATE

(
DISTINCTCOUNT('Fact'[ID]),
'Fact'[ID]<> BLANK(),
FILTER (
ALL ( 'Fact'[NameB] ),
CONTAINS (
VALUES ( 'DimName'[Name] ),
'DimName'[Name] ,
'Fact'[NameB]
)
)

The active Filter is based on NameA and the above measure is not able to override NameA filter (Even if I use ALL ( 'Fact'[NameA] inside Filter or outside )

Is there any way to override the Filter here or in general can you suggest the optimal/best approach to solve this issue? (For example having 2 different Fact Tables with simplified relationships to the 2 Dims)
)