Importing a subset of another dataset

[marcel97]

Hello all,

Our corporate IT imposes some restrictions on reports once European employees are part of the report or the dataset. Those limitations do not exist for American employees however.

In order to be compliant with the regulation I want to create a separate report for our American colleagues only, where the dataset and the report itself are limited to the data of the American employees only. The original dataset however contains the data of European employees and the American employees.

My idea would be to copy a subset of the dataset where I only have data of the American employees (I would use my Resource Master Table and limit the import to those entries, where Region = NA e.g.).

I have experimented with a Analysis Services Connection; I can only live connect to the data set however (so no transforming of the data possible) and my organization won't let me import the data set: I get the following error message:

When trying to switch to Microsoft Account I get the error message that I cannot be logged in.

Is there an alternative way to achieve what I am trying to do? Or this there maybe a solution to my login problem?

Any help is much appreciated! Thanks!

Kind regards

Marcel

[v-jingzhang]

Hi @marcel97 

I'm afraid it is not supported to import a subset of a table from an existing Power BI dataset. Currently you can connect to a Power BI dataset with Live Connect or DirectQuery. However, neither of them can let you filter the data that comes into the model. To only have data of the American employees, you need to filter data in Power Query Editor. Power Query Editor is the tool for cleaning data before importing data into the data model. However, when you connect to an exisitng dataset with Live Connect, you cannot modify the data model. When you connect to it with DirectQuery, you cannot modify the data cleaning process of the table. 

To only have data of the American employees, you need to connect to the data source to create a separate dataset. After connecting to the data source, using Power Query Editor to filter out data except American employees' data. You can build a separate report on it directly. 

Or if you don't want to query data from the data source for every report separately, you can try Power BI dataflows in Power BI Service. You can create a dataflow to connect to the data source and import all data into it. You need to apply a refresh for the dataflow to make it have data. Then connect the separate reports to the same dataflow to get data. In the report for American employees, filter data with Power Query Editor. 

I hope this would be helpful. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@marcel97 , Try if RLS can help, Just create a role with filter of America and Europe and assign them to use

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

How to use Row Level Security (RLS): https://youtu.be/NfdIA0uS6Nk

[marcel97]

@amitchandak  RLS would have been my first choice for this also. However, IT wants me to have a dataset without the data of the European employees to proceed, so duplicating or importing a subset of the exisiting dataset is the only option I can think of.