Forum Discussion

Uhrond's avatar
Uhrond
Frequent Visitor
4 years ago
Solved

DAX For RLS

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

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    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 [email protected]. 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.

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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's avatar
      Uhrond
      Frequent Visitor
      4 years ago

      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. ğŸ˜•

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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 [email protected]. 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.