Changing Dashboard Data Source Dynamically

[AutoJL]

Hi!

I need some conceptual hepl and I am not sure if I should post here or on the Service subforum. If It so happens I shoudl have done it in the other forum please let me know and I will repost there ( or move the post if it is possible).

So I have built a Dashboard where the data source is a sharepoint folder. The aim is that colleagues can drop their data into this sharepoint and then go into the powerbi service and with a button for a powerautomate app and a dataflow I have created just click the button and refresh the data source. This updates the report accoring to their customer data so they can use the report to gather insights.

So this part works perfectly but I want to make it a little bit better since, as you can imagine, this has a "pretty big" flaw which is of course that only one person can use the dashboard. My idea (as of now) is to create subfolders in this sharepoints for each customer, be able to bring up an initial list on the dashboard that shows all the names of the folders, pick one and then "somehow" modify the datasource path to append the selected folder so that the data is brought from the chosen folder. This would allow multiple people to use the dashboard without messing with each other.

Ive thought of dynamic M parameters but I am struggling to actually have a clear idea of how to do this. Does anyone know how to do this or if there is actually already a feature that would allow me to solve this?.

Many thanks!

[AutoJL]

Hi @v-yueyunzh-msft !

I see what you mean. I guess there is really no option to achieve the desired functionality using imported mode other than basically one report ( and hence dataset ) either per person or by customer which in both cases is way too many instances to take care of ( basically any change in the report would need to be manually replicated in every instance correct? ).

If I am to switch to direct query, do you ( or anyone that can help) know of any data source that can use this mode and is not strictly a database environment? Inputing data into a database is a lot more complex and would most certainly need of another set of transformations/cleaning of the data in order to do it, thus delaying the process.

Many thanks again!

[v-yueyunzh-msft]

Hi , @AutoJL 

Yes, for Power BI reports can only be bound to one dataset, not multiple datasets at the same time. If you change one report, you need to change the other reports at the same time. And the current way to modify the data source path under Power BI import mode is only parameter in my experience.
And for your needs, you want different developers to use different data, convert to DQ mode method, I think it is also necessary to maintain the corresponding permission relationship and create multiple datasets, which is not a good method.

And Power BI Direct Query is only supported by a few data sources, unlike Import Data, which is supported by all sorts of data sources. A Direct Query connection to an Excel File is not possible. Direct Query mode is usually supported by data sources that are relational database models or have a modeling engine.

For more information, you can refer to :
Understanding Direct Query in Power BI for Beginners (powerbiconsulting.com)

For different users to display different data in one dataset, the best way is to use RLS to manage:

Dynamic Row Level Security (Dynamic RLS) - Microsoft Fabric Community

Row-level security (RLS) with Power BI - Power BI | Microsoft Learn

And recently Power BI also provides a new way for developers to develop pbip, combining Power BI with Git, you can also check out this feature is all available to meet your needs：

Overview of Fabric git integration - Microsoft Fabric | Microsoft Learn

Manage a workspace with git. - Microsoft Fabric | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[AutoJL]

Hi @v-yueyunzh-msft 

Many thanks for the feedback. Let me add some information to clarify a bit more the scenario.

I indeed have the data refreshing solved with a power Automate action, that is so far working correctly. What I am concerned now is how to make the system work for many people at the same time. I had more or less come the conclusion that I needed to use parameters for this and I have a fair part of the way clear on how to do it but there are 2 main inconvenients:

- Since I am using Sharepoint folder as a data source I cant use dynamic M parameters ( I do not get the "Bind to parameter" option in the model view since this data source is not Direct Query).

- I am not sure on how to update the parameters. UI in PowerBI service doesnt seem to be an option since it does not show to the final users (my colleagues) as they are not the owners of the dataset, also it is not the most user friendly and neat option. Lastly for the REST API this is totally new world for me (also in the Learn article it states you have to be the owner of the dataset in order for this to work). I dont think my colleagues hace the technicall prowess to use powershell and run this commands. The only potential workaround for this would be to implement a python script ( I do have some python knowledge) in the report that could run a post command but I dont know if I can do something with it in the report itself.

Regarding credentials or RLS that is not a concern at all. The issue is not related to people having a certain access or belonging to a specific region or country. 

In summary the concept of this "tool" is:

My organization has service specialists that take care that customer performance is as expected and some level of proactive work is expected.

I want to develop a tool where these specialists gather the information from whatever customer they are currently focused on ( this can change with time, as they finish one task they will move to another customer, and they are not bound to a specific one), they input the information onto a sharepoint folder structure and then log into PowerBi service, hit the refresh data button and get their report ready witht all the visualizations that help them gather insights.

Up to here this tool is fully working ( still in test phase) but the next challenge is:

Tom is working with customer A data, he loads the data and refreshes the report for him to work.

Jerry is working with customer B data, he loads the data (removing Toms customer data in the process) and refreshes the report.

Tom is going to eventually finds his report destroyed because Jerry loaded different data and started wotking with it.

To avoid this I thougth of the multifolder structure inside the Sharepoint and Tom would have a folder A ( for customer A data) and refresh the report telling it that its data source is Folder A while Jerry would load his data into Folder B( customer B data) and refresh the report telling it that its data source is Folder B.

I dont even know if this would actually work but I am actually struggling to even make it possible.

 I am still searching through all the options to see if I come up with something. Many thanks for the help!!

[v-yueyunzh-msft]

HI,  @AutoJL 

Thanks for your quick response!

For your questions, this is my understand:
(1)Since I am using Sharepoint folder as a data source I cant use dynamic M parameters ( I do not get the "Bind to parameter" option in the model view since this data source is not Direct Query).

When we create a variable in Power Query, we can directly use the variable name to refer to the variable we created and you do not need to use the UI to select , like this:

 

(2)That's right, if we have created a parameter, we need to modify it in the UI interface or using the REST API.

 

(3)For your scenario, you seem to be designing for people who develop reports. You want different people to create reports in different data environments. First of all, for imported datasets, a dataset can only have one copy of imported data. Even if you modify the path of the data source through M query, the previous data will be overwritten after refreshing. We can indeed implement the operation of switching data sources after refreshing, but we cannot allow different people to see different data at the same time for development.

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[v-yueyunzh-msft]

Hi ,  @AutoJL 

According to your description, you have created a button to trigger the refresh by Power Automate and now you want to relaize the dynamic update the M query to make the customer can refresh the data for themselves.

First of all, I'm sorry that I don't know much about Power Automate. But in terms of Power BI, the way you want to dynamically modify the M query to modify the data source is to create a paramter.

For how to create the paramter you can refer to :
Parameters - Power Query | Microsoft Learn

And we can update the paramter in Power BI Service by the UI interface and REST API:
Edit parameter settings in the Power BI service - Power BI | Microsoft Learn

Datasets - Update Parameters In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

For my understanding, you can try to ask whether Power Automate can identify the current login user information, and then modify the corresponding Paramter to modify the M code according to the user information judgment, and then trigger data refresh. And i also search some document if it can help:
Call Power BI Rest APIs with Power Automate (vahiddm.com)

Solved: Get executing user in Flow - Power Platform Community (microsoft.com)

Secondly, there is another way for users to see their own data is RLS. You can import all the required data, and then configure corresponding RLS rules for different users, so that different users can view different data.

Dynamic Row Level Security with Power BI Made Simple - RADACAD

Row-level security (RLS) with Power BI - Power BI | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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