Provide user an option to select on Power BI Report

[chayanupadhyay]

Hi All,

I have one report whose data source is Excel workbook, the same report is used for different country, now i need to automate this report so user has an option to select the country through dialog box (if possible) and if dialog box is not possible then atleast from slicer and based on user selection the relevant file data is loaded.

Suppose i have two countries data:

1. One excel workbook for AU

2. One excel workbook for UK

3. I have created one Excel file which has country name and its file location, PFB the sample

Country Name	File location
Australia	C:\Users\abc\xyz folder\AU.xlsx
Great Britain	C:\Users\abc\xyz folder\GB.xlsx

i have created a standard report using the UK data (i have imported UK data and created the report) but i need to give user an option to select the country through slicer or Dialog box and if user select AU then same report should show AU data.

After importing UK data in report, i have done transformations(Transpose and remove columns and remove rows , split columns etc.) when user give AU as a selection, AU data should be loaded and all the transformation should be performed on AU data which i did for UK data.

I haven't worked on Parameters in Power BI but as per my knowledge Parameters can be used within Power BI Desktop but i need this functionality in Service - "please correct me if my understanding is wrong"

Please share steps or links i can go through if we can achieve this with the help of Parameter.

If not with parameters then please suggest alternate ways to achieve this.

Hopefully above explaination is making sense if not then please let me know i will explain the problem in more detail.

Thank you for your help and support.

Regards,

Chayan Upadhyay  

[v-lili6-msft]

hi, @chayanupadhyay 

First, you could set Parameters in Power BI desktop then edit Parameters in Power BI service.

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

https://community.powerbi.com/t5/Community-Blog/Using-the-Power-BI-Service-Parameters-to-change-conn...

Second, for your case, I think you could try this way as a reference:

Step1:

get data from folder power bi to import all the excel file.

https://docs.microsoft.com/en-us/power-bi/desktop-combine-binaries

https://www.jetglobal.com/blog/power-bi-load-data-from-folder/

Step2:(optional)

Then you could set a Parameter in there to filter excel name.

Step3:

After combine and shape the data source, then in the data model, you could set a RLS or a slicer based on excel name column to choose the data. (When you expand the excel data, please keep excel name column, don't remove it).

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

Best Regards,

Lin

[chayanupadhyay]

Thank you @v-lili6-msft  for your answer.

I am able to setup the parameter with in Power BI, now i need one country slicer which should be in sync with the Parameter, if i change the slicer value from Great Britain to Australia the parameter should also change and pick Australia file.

"After combine and shape the data source, then in the data model, you could set a RLS or a slicer based on excel name column to choose the data. (When you expand the excel data, please keep excel name column, don't remove it)."

Can you please explain little bit more on this apart from RLS since i can't set RLS on this data(no business requirement and no RLS related data).

Thank you again for your help and time.

Regards,

Chayan Upadhyay