CODEWITHSUNDEEP
powerbidaxrow-level-security
variable
variable

Reputation: 9736

How to filter a dimension based on RLS applied to another dimension?

Assume I have 3 dimension tables and 1 fact table.

DimUser

DimClients

DimCompany

FactSales

All the dims have a 1:M relation with filter flowing from the 1 -> M side.

The Microsoft documentation on RLS shows how to create RLS for one dimension table, and that filters the fact table. But I can't seem to find a way to filter the other dimensions.

Assume I have configured RLS filter on DimUser ([Username] = USERNAME()) so that the user of the report can see his own data based on email address, so user can see his own record entry in DimUser. Thus it will auto filter the FactSales also, effectively the user will be able to see only his sales.

However the user can see all clients and companies in the slicer. I want to setup filter on the DimClients and DimCompany so that the user can see only clients and conpanies for which he has made sales. How can I achieve this?

For example:

  1. How to apply RLS to the DimClients and DimCompany so that it will filter only those ClientIds that appear in the sales table based on dynamic filter [Username] = USERNAME() applied on the DimUsers table?

  2. The other option is to enable bidirectional filtering but this is not allowed for multiple tables.

Upvotes: 0

Views: 1677

Answers (2)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

You can create a CC in your dimension to check if a client has sales associated with the current user.

ClientVisible = 
CALCULATE(
    COUNTROWS('FactSales'), 
    USERELATIONSHIP('FactSales'[ClientId], 'DimClients'[ClientId]),
    FILTER('DimUser', [Username] = USERNAME())
) > 0

Also,I would go for creating a CC that filters companies based on whether there are sales records in FactSales related to the current user.

[CompanyVisible] = 
CALCULATE(
    COUNTROWS('FactSales'), 
    USERELATIONSHIP('FactSales'[CompanyId], 'DimCompany'[CompanyId]),
    FILTER('DimUser', [Username] = USERNAME())
) > 0

Even if you cannot use the CC in RLS, I can say it still represents the logic you need to implement. If you share some data, I can dig more on what you want to achieve.

Update :

ClientIDsForCurrentUser = 
CALCULATETABLE(
    DISTINCT('FactSales'[ClientId]),
    FILTER(
        'FactSales',
        'FactSales'[UserId] IN SELECTCOLUMNS(
            FILTER('DimUser', 'DimUser'[Username] = USERNAME()),
            "UserId", 'DimUser'[UserId]
        )
    )
)

The CALCULATETABLE will generate a table of unique ClientIds filtered by the current UserId. Then, the FILTER is applied to ensure that only sales records associated with the current user identified by USERNAME() are included.

Upvotes: -1

Ryan B.
Ryan B.

Reputation: 3665

You can create a measure that looks like this:

UserFlag =
CONTAINSSTRING (
    CONCATENATEX ( FactSales, RELATED ( DimUsers[UserEmail] ), "," ),
    USERNAME()
)

Create your RLS role and add this filter to each dimension.

[UserFlag] = True()

The result: If a row from a dimension is related to a fact row that is in turn related to the DimUser row whose email matches USERNAME(), then the measure returns 'True.' And so RLS is filtering only to those rows.

Upvotes: 1

Related Questions