How to filter a related table

[Anonymous]

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,

[v-zhenbw-msft · Accepted solution]

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.

The result like this,

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]

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

[v-zhenbw-msft]

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]

@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]

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]

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