RLS not working inactive relation and measure

[Anonymous]

I just implemened RLS in my report. Some visuals show an error:

I have a table "accountM":

Accountmanager | email

John                  | john@power.bi

This table is connected to the "bezoekrapport 5-2" with an inactive relation on "accountmanager".

This relation is inactive, because through an other table the "accountM" table already has a relation with table "bezoekrapporten 5-2"

The relation is used for a visual that counts the amount of visits made by an account manager.

To do this I have used the following measure:

Count bezoeken = 
  CALCULATE(COUNT('Bezoekrapport 5-2'[Outlet]);
      USERELATIONSHIP(accountM[Accountmanager];'Bezoekrapport 5-
      2'[accountmanager]

What can I do to still make this work?

[Anonymous]

@Anonymous,

instead of using userelationship() function, use ALL and FILTER manually to create the measure. There is a similar thread for your reference.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/aae38bbc-a4bf-4e86-97f1-c89dc65c1867/userelationship-function-cannot-be-used-while-querying-table-mytable-because-of-the-row-level?forum=sqlanalysisservices

Regards,
Lydia

[Anonymous]

Hi @Anonymous

Thank you very much for your reply.

I tried to use Filter and ALL:

Count bezoeken = 
  CALCULATE(
     COUNT('Bezoekrapport 5-2'[Outlet]);
       FILTER(ALL('Bezoekrapport 5-2'[accountmanager];
           CONTAINS( VALUES(accountM[Accountmanager]);accountM[Accountmanager];
                    'Bezoekrapport 5-2'[accountmanager] )
                                )
                                )

However it does not display the same number as when I use the USERELATIONSHIP(). Can you tell me what I should change?

[Anonymous]

@Anonymous,

Please share dummy data of your table for us to analyze.

Regards,
Lydia

[Anonymous]

@Anonymous

Table 1:

Account M
Person A
Person B
Person C

Table 2:

Company	Visit date	Accountmanager
Company A	01-01-17	Person A
Company B	02-01-17	Person A
Company C	03-01-17	Person A
Company D	04-01-17	Person A
Company B	05-01-17	Person A
Company C	06-01-17	Person A
Company A	07-01-17	Person B
Company E	08-01-17	Person B
Company C	09-01-17	Person B
Company A	10-01-17	Person B
Company D	11-01-17	Person B
Company C	12-01-17	Person B

I would like to count the amount of visits per account manager. 

I can not make an active relation with account M and account manager, because account M already has a infleunce on Account manager through an other chain of relationships.

[Anonymous]

@Anonymous,

Create the measure in Table 1 using your two different DAX, they return same result when dragging the Account M filed of Table 1 and the measure in a visual.

Regards,
Lydia