Looking for a way to filter dimensionalized dataset

[Anonymous]

I have two tables:

1. employee name with project name

2. project name with project details

I would like to either use RLS or a parameter in the first table based on the employee name so I can limit the amount of records that are imported when being refreshed.

I tried the parameter filter in the select statement of table 1, but that doesn't seem to filter anything in table 2.

I also tried to use RLS using a filtered version of table 1 based on the parameter, linking to table 1, but that still imports everything from table 2 and completely filters out everything. This seems to work if I manually add the name in quotes, but not any other way.

The only thing I can get remotely working is just straight RLS based on username(), but it's still brining in everything from table 2.

Obviously I could combine these tables into the select statement and have everything work just fine, but doesn't that defeat the point of the data model in Power BI?

thanks in advance.

[v-yingjl]

Hi @Anonymous ,

Before you use RLS or parameters, make sure you have created relationships between these tables:

Create RLS:

Create parameters need to use table visual filters to hide blank rows:

Attached a sample file that you can refer, hopes to help you: filter dataset.pbix

Best Regards,
Yingjie Li

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

[Anonymous]

I'm not sure how it works, but when I set up RLS it wasn't letting me download the file from the app space. This was something else I needed.

I ended up using a template and setting a parameter to filter the main dataset instead of one of dimensional datasets. Any attempt at using the relationships between tables to rely on the filters still ended up bringing in the entire table before it got filtered. The template made it so it asked for the parameters up front and would use that when it downloaded the datasets.

RLS doesn't seem to work with the templates either. As far as the data none of this was for security reasons it's just that it takes forever to download datasets and was trying to alleviate some of that.

[amitchandak]

@Anonymous , check out if this can help

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies