Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply

How to: User specific Anti-Join with Direct Query

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

 

1 REPLY 1
MFelix
Super User
Super User

Hi @Christian_Heinz ,

 

Believe that the best person to answer you to this one is @ImkeF  best M language user in the community.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.