Filtered Rows in Advanced Editor doesn't apply to Roles

[SHS]

Hello,

I'm having an issue with the filtered.rows script in Advanced Editor (Power Query Editor). I've created script in which I want to remove all "inactive" accounts from my dataset by using below script:

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([ID] <> null and [ID] <> "") and ([Status] = "Active")),

Based on the result seen in my table, the script works as all inactive accounts are excluded. However, this doesn't apply for all users. E.g., looking at my data through a specific subsidiary the script is working and I'm not able to see data linked to inactive accounts. When I'm looking with a global access; hence having access to see all data I can still see the data linked to inactive accounts - how can that be? Is it an error in above description?

[dax]

Hi SHS, 

You siad you could view all data, in report ?Or in advanced editor? If in Advanced Eidtor, because the "Filter Row " is a step, so you could click "source" to view all data. This is by design.

If in the report, you could try to refresh the report or re-create report to see whether it works or not.

By the way, did you set role on data? Is inactive data in linked table or in the same table which apply "Filter Row"? If possible, could you please inform me more deailed information?

Best Regards,
Zoe Zhi

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

[SHS]

Hi @dax 

I can view all data in the report (even though I've made the script asking to exlude all inactive customers for the data).

I have tried to refresh the data and the report a couple of times, without any luck. 

I'm a bit new to Power BI still but I will try and explain as basic and precisely as possible

The report I have aim to get an overview of the market situation, mainly visualizing the market share within multiple product groups. This is done by having a market share file, containing all opportunities we have in CRM. Additionally, we have the customer data file containing all basic internal and external information about each customer. 

The script I've made, asking to exclude/filter all inactive customers from the data is done via the customer data file, while most of the data we visualize in the report is based on the market share file. Further, all the roles (based on the sales organization responsible for each account) is set via the customer data file. The linkage between these two files are many to one (cross-filter both). Additionally, we have a Global Roles for our HQ department in which these can see the global data for all customers. When we test the report through a Sales Org. role, the filter made in advanced editor works as it should, as all inactive customers and their related data in each of the file in the data model are excluded. However, when we test through our HQ role; hence having access to the global data, then the inactive customers are not excluded. The HQ role doesn't have any table filter in the role-settings - I've tried to change that by creating a filter in the customer data file, so that we only see "active" customers, but that hasn't worked as well. 

Best Regards,

Sebastian