The requirement is to filter the DATA_TABLE table based on specific column values from the USER_FILTERS table for each logged-in user. The filtering should be based on multiple attributes, Use Row-Level Security (RLS) with USERPRINCIPALNAME() to filter data based on the logged-in user Match the USERNAME column in the USER_FILTERS table with the USERPRINCIPALNAME() of the logged-in user.
Let's break it down:
If a value is NULL or ALL:
The user should see the ENTIRE DATA for that attribute.
In other words, if the filter value is NULL or ALL, the user should have UNRESTRICTED ACCESS to all values for that attribute.
If a value is SPECIFIED:
The user should only see the SPECIFIED VALUE for that attribute.
In other words, if a specific filter value is provided, the user should only have ACCESS TO THAT SPECIFIC VALUE for that attribute.
To illustrate this, let's consider an example:
Suppose we have an attribute called "LOB" (Line of Business). If the filter value for LOB is:
NULL or ALL, the user should see ALL LOB values (e.g., LOB1, LOB2, LOB3, etc.).
"LOB1", the user should only see data for LOB1, and not for any other LOB values.
A many-to-many inactive relationship exists between FILTERS and DATA_TABLE on those attributes.
using below it does filter but when i combined for all the filter attributes it does not work, either i see blank or all the values.
VAR CurrentUser = USERPRINCIPALNAME()
VAR LOBFilterValues =
CALCULATETABLE(
VALUES('USER_FILTERS'[LOB]),
'USER_FILTERS'[USERNAME] = CurrentUser
)
RETURN
COUNTROWS(LOBFilterValues) = 0 ||
"ALL" IN LOBFilterValues ||
'DATA_TABLE'[LINE_OF_BUSINESS] IN LOBFilterValues
