Automatically update usage statistics across multiple reports

[Anonymous]

Hello everyone!

I have two premium workspaces (A&B) with two different reports in each one of them.

I have the usage statistics (auto generated by PowerBI) for each of the different reports separately in their respective workspaces. 

What I am trying to do is to create a combined "Usage Statistics" report that will combine the data from the two different usage statistics reports from the two workspaces into one.

Only way I can think of making this work is by exporting the csv files from the usage statistics reports and combine everything  into a separate report that I build on PowerBI Desktop. Only issue is that the files needs to be manually downloaded everyday and my local report refreshed manually.

Is it possible to automate this process? Can I connect straight to the underlying PowerBI dataset that is used to generate the usage statistics reports?

[_sfrost]

@Anonymous 

This is possible. Thanks to the latest feature of Power BI, DQ for datasets, which allows you to connect to a dataset in Direct Query mode.

Whenever you generate usage metrics for a report, a dataset will be created in the same workspace named "Usage Metrics Report"

Once you generate the usage metrics in both workspaces, follow the below steps:

1. Make sure you have the latest verison of PBI Desktop and enabled the DQ for datasets preview feature.
2. Open PBI Desktop and click on Power BI datasets source and it will allow you to select a dataset
3. Now search for "Usage Metrics Report" in the search box. (It will list all the available Usage Metrics dataset from all the workspaces)
4. Select the datasets from your desired workspace1
5. Repeat the process for the next Usage Metrics Report dataset from workspace2

Now you can build your own report.

Did I answer your question? Give a thumbs up and accept this as solution!

[Anonymous]

Thank you for this!

Few things I noticed, it seems like I can't do any manipulation within Power Query as in appending datasets/pivoting/unpivoting

Secondly, it will only let me add PowerBI from one source only, i.e. once a a PowerBI dataset is in, I can't bring in anything else. Not sure why its doing that

[_sfrost]

@Anonymous 

Did you enable the preview feature 'Direct Query for dataset' in desktop settings? That could be the reason why you are not able to add another dataset. I just tried and I was able to add multiple dataset connections in DQ mode.

And regarding appending tables, may be you can make use of Union() DAX function.

If not, seems it's possible to acheive this requirement with paginated report as explained in the below article.

(1) Automate Power BI Usage data across workspaces using Paginated Reports | LinkedIn