DAX For RLS

[Uhrond]

Hello everyone, I need an expert in DAX because I have trouble understanding the logic of it.

I'm trying to implement the RLS in my Power BI report.

I have a nested set model as follow : 

Which is stored in a first table :

Then my data table that I want to filter also containing left and right values :

So when "C" connects to the report ("C" comes from USERNAME() ), I want my data table to be filtered like this :

Because "C" has access to all data that has a left greater or equal than 3 and a right less or equal than 4
AND the data that has a left greater or equal than 8 and a right less or equal than 13.

This is what I want to convert to DAX.

In this example I only have two lines for "C" but I can have more.

Thanks for your help, hope this is clear enough

[Uhrond]

Thanks for spending time on my problem.

[Greg_Deckler]

@Uhrond I don't believe you have enough information in your tables to dynamically create/enforce the rules you have laid out. For example, there is nothing that indicates that the C rows should be less than versus greater than. Also, your comment about more than two rows is concerning because not sure how that would work.

[Uhrond]

Thank you for your answer.

I'm a developer and used to algorithm logic. What I want could be done with this simple code :

foreach(dataRow in Data)
{
	foreach(struc in StructureTable)
	{
		if(dataRow.Left >= struc.Left && dataRow.Right <= struc.Right)
			return true;
		else
			return false;
	}
}

I know DAX doesn't work the same at all. But I thought there had to be a way to do it. 😕

[v-rzhou-msft · Accepted solution]

Hi @Uhrond ,

According to your screenshot, I think the hierachy level in your data model will make calculations difficult.

We can see that C is the parent in level 2 and C is also the child in level 3. This will confuse Power BI. Secondly, there are multiple ranges in C, I think it is better for you to expand the numeric range by create a new calculated table.

Basic User Table:

Basic Data Table:

Now, let's create new User Table and Data table by Dax.

Expand User = 
GENERATE (
    SUMMARIZECOLUMNS (
        'Basic User'[Name],
        'Basic User'[UserName],
        'Basic User'[Left],
        'Basic User'[Right]
    ),
    GENERATESERIES (
        CALCULATE ( DISTINCT ( 'Basic User'[Left] ) ),
        CALCULATE ( DISTINCT ( 'Basic User'[Right] ) )
    )
)

Expand Data = 
GENERATE (
    SUMMARIZECOLUMNS ( 'Basic Data'[Data], 'Basic Data'[Left], 'Basic Data'[Right] ),
    GENERATESERIES (
        CALCULATE ( DISTINCT ( 'Basic Data'[Left] ) ),
        CALCULATE ( DISTINCT ( 'Basic Data'[Right] ) )
    )
)

Build a relationship between [Value] column in two tables. Trun on "Apply security filter in both directions" due to many to mant relationship.

We can get the user who login Power BI by USERNAME() or USERPRINCIPALNAME(). Here I suggest you use USERPRINCIPALNAME(), it will get email format username in both Power BI Desktop and Service. 

Mange Roles:

Now let's see the report as C@xxx.com. Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.