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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to filter a related table

Hello,

 

I have a Dim.Organization table and a related table named Dim Organization Top Level

 

Dim Organization TopLevel = RELATEDTABLE('Dim Organization')
 
In the Dim.Organization table, among the fields available, we have the OrgSkey.
So, when I filter the Dim.Organization Table using some selected OrgSkey,  I would like to have the related table filtered so it the OrgSkey values in both table matched.
 
Is there a way to do that?
Regards,
 
1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can use the following RLS formula to meet your requirement.

 

[OrgSkey] IN 
SELECTCOLUMNS(
	FILTER(
	'DimOrgSecurity',
	'DimOrgSecurity'[userid_upn]=USERNAME()
),
"ID"
,'DimOrgSecurity'[OrgSkey]
)
[userid_upn] = USERNAME()

 

Put them to three tables like the following screenshots.

 

How 1.jpg

 

How 2.jpg

 

How 3.jpg

 

The result like this,

 

How 4.jpg

 

And you can also refer this article.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

BTW, pbix as attached.

 

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can use the following RLS formula to meet your requirement.

 

[OrgSkey] IN 
SELECTCOLUMNS(
	FILTER(
	'DimOrgSecurity',
	'DimOrgSecurity'[userid_upn]=USERNAME()
),
"ID"
,'DimOrgSecurity'[OrgSkey]
)
[userid_upn] = USERNAME()

 

Put them to three tables like the following screenshots.

 

How 1.jpg

 

How 2.jpg

 

How 3.jpg

 

The result like this,

 

How 4.jpg

 

And you can also refer this article.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

BTW, pbix as attached.

 

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hello

 

Thank you very much for you help.   It is exactly what I need to get.  

The only thing that I don't understand, is the naming the variable or field 'dimOrgSecurity'[OrgSkey] as "ID" but this name field does not appears anywhere.  Could you please explain.

 

Regards,

Alain

Hi @Anonymous ,

 

The ID in double quotes is a custom name, it is a virtual new column added for the virtual table.

You can refer this article to understand the SELECTCOLUMNS function.

If you have any question, please kindly ask here and we will try to resolve it.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , If the tables are related, it should filter that data. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Anonymous
Not applicable

Hello,

 

I have made a demo project where we have 3 tables.  DimOrgSecurity where we define which orgskey a user can have access to, a dim.org table which contains a list of orgskey and finally a table named table1 = relatedtable('dimOrg').

 

So What I would like to do is, when I view as b@test.com, I should see only 3 OrgSkey in DimOrgSecurity and due to the fact that DimOrg is linked in both direction, this table is filtered and show the same number of records as in DimOrgSecurity.

 

What I would like to do, is to find a way to filter the table1, a relatedtable('DimOrg'), so it show the same number of records and hopefully the same orgskey.

 

So my question is there a way to do that.  

Also, How can I share my demo project with the community?

 

Regards,

Anonymous
Not applicable

Here's the demo project (pbix) if someone wants to look at it and may be propose a solution.

 

https://1drv.ms/u/s!At4hIgQFrUxYjU5ukjQHpKaVxXtz?e=OjvrR7 

 

Regards,

Alain

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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