This is not currently possible to do exactly what you want. You can dynamically change a data source and update the query used in a specific report but there isn't an API available (PowerBI REST or PowerBI JavaScript) to update what columns are on a visual in a report. You can get pretty close to what you want but the report cannot be displayed in View
mode and it will have to be displayed in Edit
mode so the user will have the ability to drag the fields generated from their updated DAX query results onto the Matrix visual manually if they submit a change.
Assuming you already have the pre-requisite App Registration setup and configuration completed and your ready to embed here are some steps to get close to what your looking for. If your not ready to embed there is some documentation below the 8 steps I provided to complete the pre-requisite setup to be able to embed.
- Using PowerBI Desktop Create a template report that has a matrix visual and a connection to the data source you want to use. Be sure to set this report up using a specific query and NOT all tables in the data source whether its SQL or SSAS. (You specify the query under advanced options when you initially setup the data source in the report)
- Setup a Power BI Data Gateway to the Data Source your report uses
- Create a workspace on PowerBI.com to upload the report to
- Using PowerBI desktop Publish the report you created in step 1 to the workspace you created in step 3
- On your Web Application, when a user views the report viewer page, you need a way to identify each user. Lets assume you have
UserId
field that is a unique ID for each user. Call GetReports in group https://docs.microsoft.com/en-us/rest/api/power-bi/reports/getreportsingroup. You need to have a known report name plus the UserId. Lets assume its DynamicReport. So call GetReports and check if DynamicReport_UserId
exists for the user trying to view the report viewer page in your web application.
- In that API calls result from step 5, if the report does not exist for a user, use https://docs.microsoft.com/en-us/rest/api/power-bi/reports/clonereport to clone the template report you published in step 4.
- Embed and display user specific template report (DynamicReport_UserId) for the user.
- Have logic on the report viewer page so the user can submit and
POST
a DAX query. When they do a submit have logic to use https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updatedatasources on the back end to update the data source in their report and then embed the report again with their DAX statement and changed data.
The visual isn't going to automatically update to the new fields from the new query that is submitted by the user but the available data fields they have in editor mode will change. The user will have to drag and drop the fields from their DAX query onto the Matrix visual or whatever visual type they are going to chose to use. You won't be able to just display a report in View
mode since you don't have a way to programatically update what fields are on visual. The user may even end up seeing a broken visual initially because of the changed query and the visual referencing fields from the previously used query. You could use PowerBI Javascript API to hide the existing visual to improve the user experience of the user not seeing something broken.
Let me know if you have any specific questions about these API calls or how to use them.
Here is where I've been looking for the Power BI JavaScript functionality https://github.com/Microsoft/powerbi-javascript/wiki (Can't find anything specific to matrix Visuals. The JavaScript functionality for visuals generic to Visuals and not Visual Type like Matrix or Card)
Here is Microsoft's documentation on the available REST APIs and https://docs.microsoft.com/en-us/rest/api/power-bi/
Here are some good resources to learn more about embedding
https://docs.microsoft.com/en-us/power-bi/developer/embedded/register-app
https://docs.microsoft.com/en-us/power-bi/developer/embedded/embedding
If you need drill down capability Hierarchies are a great option
https://spreadsheeto.com/power-bi-hierarchy/
DirectQuery
or static dataset? If you need few predetermined views (e.g. TopN branches, where user provides the N), then you can achieve it without repopulating the data – Stachu