Hi there,
i am currently searching for a solution to implement an Anti Join in BI with a user context & direct query. I tried a lot and did'nt find a solution.
The scenario is the following:
- We have a table containing Issues (messages)
- i want to hide some messages by ignore list (the classical Anti-Join)
- This ignore list is changed dynamically, so the resulting data should not be in Import-Mode
The data model is
- table A containing all issues
- table B, containing the user specific ignore list, (user-id, irgnore-issue-id)
So the requirement is that if one message is added to the ignore-list for a user, the user should not see the message in BI
I tried several solutions (e.g. the DAX Except statement and the Join Query function), but that doesn't worked.
My idea was to use dynamic M query parameters and call a function giving the user Principal Name as parameter, e.g.
let
Quelle = AzureDataExplorer.Contents("https://xxxx.westeurope.kusto.windows.net", "public", Text.Combine({"MyTable(""",PrincipalName,""")"}), [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null])
in
Quelle
Since "USERPRINCIPALNAME()" cannot be used in an M-Query (Unfortunately!!!), I used the Parameter "PrincipalName" bound to a table containing all PrincipalNames, selected by Row Level Security using "USERPRINCIPALNAME()" as filter.
The solution works except one thing: The parameter "PrincipalName" is only set if a row in the RLS Table is manually selected by the user. The RLS functionality alone does not change the parameters value.
Is there anybody solved this in one or another way?
Thank you in advance,
Christian
