Filtering data in Edit Queries not the same as using slicer

[andrmh]

Hi all,

This may be a little confusing.

I have 2 reports.

Report 1, is a 'Master' report, to be used internally. It contains an 'options/summary' page, which includes a slicer (single select) on Customer. Customer is a view of its own for schema purposes.

Also within this is:
* an Orders View, which is a sql view relating to various order entries, it represents all orders.

* an open orders view, which is effectively the same as the orders view, but setup to yield only open orders

The relationships, defined in power bi, not sql, look like:

With the customer slicer tables and data filtering on Open work orders works well.

Report 2, we want to be a client specific report, which we intend to give to the client, using the report 1 as the base report.

To achieve this, I went to 'edit data' (we are in direct query mode), and specifically stated the client code on the customer table.

I thought this would achieve the same as the slicer.

But it does not.

Measures and data displayed in the report taken from Orders or Open Orders tables, are still seeming to include data from other clients.

Assume referential integrity is enabled on all relationships.

Data example:
In edit data i hard wired customer ABC (shown in first table
Yet data displayed from Orders is still showing all....

What have I done wrong here?

Being able to have the same 'base template' report and then simply tell the query data to hard wire a filter would be the best way forward.
We are strictly against applying filters at the visual/page/report level as the client could change it.

Thanks,

[v-juanli-msft · Accepted solution]

Hi @andrmh

Your requirement is to let users see data which they have pemission to, for example, user A can see data about user A, but can't see data about others.

If you are in this scenario, you could consider setting RLS on the report, thus dataset is limited to users.

Reference:

https://docs.microsoft.com/en-us/power-bi/service-admin-rls

additionaly, if my understanding and suggestion are not right, could you tell me how did you do the following?

specifically stated the client code on the customer table

Best Regards

Maggie

[andrmh]

Thankyou

This will work for us.

RE: specifically stated the client code on the customer table

I specifically went into "Edit Queries" in power BI Desktop, added a filter on the customer table to the specific customer I wanted.

This meant the customer table only held a single row. (the rest were filtered out)

However, my related Orders table still had all customers in it, dispite the integrity rule on the relationship.

Now, i could go to that table and filter it also, to get the result i need, But that really starts to get messy going forward.

Row based security does work much cleaner.

I am curious why the relationship rule was not followed however.