RLS based on Date Range

[JB_AT]

Good evening

I want to implement a RLS solution that uses USERPRINCIPALNAME and a date range.

I have an RLS table that has the Email of the people who are allowed to see certain parts of the dataset. There is also  FromDate and EndDate columns. The RLS table has a Relationship with the Sales table. 

The DIM Date also has a relationship with the Sales table. 

The USERPRINCIPALNAME by itself works, but I would like it to only show data for the times between the FromDate and ToDate from the RLS table

Is this possible?

Thanks in advance

[Anonymous]

Hi @JB_AT ,

You can use the following formula to filter the time data between FromDate and ToDate in the RLS table:

=IF(
    AND(
        Sales[Date] >= RLS[FromDate],
        Sales[Date] <= RLS[ToDate],
        RLS[Email] = USERPRINCIPALNAME()
    ),
    1,
    0
)

Where 1 means the user can view the row and 0 means the user cannot view the row.

Best Regards,

Xianda Tang

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

[JB_AT]

Thank you @Anonymous 

I received an error  "Too many arguements were passed to the AND function. The maximum count for the function is 2"

[Anonymous · Accepted solution]

Hi @JB_AT ,

You can try this:

=IF(
    Sales[Date] >= RLS[FromDate],
    IF(
        Sales[Date] <= RLS[ToDate],
        IF(
            RLS[Email] = USERPRINCIPALNAME(),
            1,
            0
        ),
        0
    ),
    0
)

Best Regards,

Xianda Tang

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