DAX RLS Trouble

[sokatenaj]

Hi Folks,

Been trying to create this RLS model in one of my dashboards. We have 32 companies using various letter combos. Typically if people only need access to certain companies I would do:

[Company] IN {"B", "D", "Z"} 

or if they do not need access to 1 company but everything else I would do:

[Company] <> "B"

However, I have to assign a user access to not see 6 companies. I tried doing the following and it didn't work:

[Company] NOT IN {"A", "C", "E", "I", "BA", "BG"}  

The only way I can seem to get it work is if I do somethigng like: 

[Company] <> "A" && [Company] <> "C", etc

There has to be an easier way....what am I missing here? 

[v-huizhn-msft]

Hi @sokatenaj,

IN function works in DAX filter, you can use IN including all accessed companies as picture1 shown. I test it and work as picture2 shown.

picture1    picture2

For Row Level Security, please review this blog, which describe it in detailed steps.

Best Regards,

Angelia

[sokatenaj]

Hi @v-huizhn-msft,

Thanks for this. However, I know how to do that part. What I can't seem to get work right is to say it is NOT those companies. 

I want to write something along the lines of:

[Company] NOT IN {"A", "C", "E", "I", "BA", "BG"}  

I just don't understand why it isn't working. I tried it so many ways, but the only way I can get it to work is if I do:

[Company] <> "A" && [Company] <> "C", etc

That surely can't be the only way. 

[gooranga1 · Accepted solution]

I think you could use

not([Company] IN {"A", "C", "E", "I", "BA", "BG"})

[sokatenaj]

@gooranga1 That was exactly what I needed. I did not think I could add "Not()" in that context. That is really helpful and it worked like a charm. Thank you!