Filter a column without creating join in Power BI Desktop

[akj2784]

I have two tables, one uservendor relationship table and other ProductVendor table.

What I want is when I login as a user, Vendor should be defaulted to my own Vendor from Uservendor table.

And the correspondng Product slicer should have list of all products tagged to my Vendor.

However I don't want to create join between these two tables as it will filter out the other vendors data which I dont want.

Because along with that I also have one more slicer which should give me list of other vendor's product.

So can we have a Product Slicer to show the products of my own vendor in a slicer ? Basically I need to filter a column without creating join.

[v-shex-msft]

Hi @akj2784,

I'd like to suggest you to create RLS with username function to achieve your requirement.

RLS with UserName()

Logic:
1. Username function will return current domain user.
2. Use above user to get filtered vendors from user table.
3. Apply filter effect on vendor table .

BTW, it will be help for coding formula if you share some sample data and table structure.

Regards,
Xiaoxin Sheng

[akj2784]

I have tried that but it doesnt work.

here is the link with sample dataset.

https://1drv.ms/u/s!AhH0hVVCLnYFaUfcoeKuFMP9QKo

[v-shex-msft]

Hi @akj2784,

After check on your user vendor table, I find your records not support to use username to find specific vendors.

You can try to create a measure to check selected users' vender.

Check Tag = 
VAR user =
    SELECTEDVALUE ( UserVendor[User] )
VAR vendorlist =
    CALCULATETABLE ( VALUES ( UserVendor[Vendor] ), UserVendor[User] = user )
RETURN
    IF ( SELECTEDVALUE ( 'Product'[Vendor] ) IN vendorlist, 1, 0 )

Result:

Notice: USERNAME fucntion will return current user  name as username@domain.com(e.g Tom@abcCompany.com)

Regards,

Xiaoxin Sheng

[akj2784]

Hi,

Thank you for putting an effort.

My requirement is to constrain the Vendor slicer to show only User's vendor which I am able to achieve using USERNAME() function.

However, the moment I add RLS to show only relevant vendor in the Vendor slicer, it actually filters other Vendor's product from the table itself which I don't want.

Because my requirment is to show list of all other Vendor's product in the Competitior Product slicer.

eg.

I am from Oracle, when I login to Dashboard, my Vendor slicer should default to Oracle. 

Its Corresponding Product slicer should display all the Oracle's product. lets say I selected Oracle Busines Intelligence Enterprise Edition(OBIEE) product.

Now I will find the Category of the selected Product which is Business Intelligence.

Once I get the category as Business Intelligence, I have to show list of all other Vendor's product in the slicer. e.g. Microsoft Power BI, IBM Cognos etc even though they are from different vendor like Microsoft and IBM respectively.

So basically I want to compare how my own product i.e. oracle's OBIEE is performing as compared to competitior Microsoft Power BI and IBM Cognos. 

Regards,

Akash

[v-shex-msft]

Hi @akj2784,

AFAIK, current RLS not support this feature, please submit an idea to ideas forum to help us improve this feature.

For your scenario, I 'd like suggest you to break relationship to keep records (it will effect current RLS filter effect) or duplicate tables in query editor as compare tables.(please remember to remove relationships to other tables to break RLS effect)

Regards,

Xiaoxin Sheng

[akj2784]

tried Column = LOOKUPVALUE( Column, Search Column, SELECTEDVALUE(Column B))

but it doesnt work . It shows null in the Column data. It does not through error.