Forum Discussion

JB_AT's avatar
JB_AT
Helper III
2 years ago
Solved

RLS based on Date Range

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      JB_AT
      Helper III
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.