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 :  ...
  • 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.

     

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.