Forum Discussion
Dynamic User related SQL WHERE clause
If you're using Import, then the only option is to load all of the data (without user filtering) and then apply RLS as appropriate on that unfiltered data.
lbendlin is correct. Import doesn't allow for dynamic querying, full stop. DirectQuery should work in this scenario, possibly even without needing to write explicit WHERE clauses if table relationships are set up correctly.
Thank you. Unfortunately, the setup table (UserNameQuery) that houses the WHERE Clause has a format like this:
Email ReportName Criteria [email protected] WSUM WHERE Company IN ('A','B','C') [email protected] WSUM WHERE Company = 'A' AND ReportingRegion LIKE 'XX%' So, what i did was to import the main table using DirectQuery. Then i went into Advanced Editor:
let
CurrentUser = UserNameQuery, //UserNameQ
ListOfUsers = Table.ToList(CurrentUser),
StringOfUsers = Text.Combine(ListOfUsers,""),
Source1=Sql.Database("xxx-container.database.windows.net", "yy-container", [Query="SELECT [QUERYCRITERIA] FROM [dbo].[Report_Criteria] WHERE QRYNAME = 'WSUM' AND [PrincipalName] = '[email protected]'", CreateNavigationProperties=false]),
ListOfParts = Table.ToList(Source1),
StringOfParts=Text.Combine(ListOfParts,""),
Source = Sql.Database("xxx-container.database.windows.net", "yy-container", [Query="SELECT * FROM dbo.v_WIRESUM_Indexed WITH (NOEXPAND) " & StringOfParts, CreateNavigationProperties=false])
in
SourceWhen i come back to the main screen and refresh data, i get the following:
Failed to save modifications to the server. Error returned: 'DirectQuery partition 'Query2-2abf1210-e4eb-4ca5-8b0b-110bfdc665fd' has '2' datasource reference(s) in its expression which is not allowed. '.
Please advise.
thanks
Ben
" what i did was to import the main table using DirectQuery"
Not possible. Use Import mode, not Direct Query.
