Publish custom usage metric report into Workspace: no gateway to access the data source file

[Lexi_Liu]

Hello!

I created a custom usage metrics report because I would like to track utilization% between my target audience, but not all users although they have access to those reports.

I have 1 excel file contains my target audience email list saved in Sharepoint, and 2 Power BI datasets for 2 usage metrics report from 2 Workspace which I have admin access. It works well in my desktop and can be refreshed when I open it.

However, if I publish into Workspace, either my Workspace or other Workspace I have access to. It shows error says "There is no gateway to access the data source File. In Datasets setting page, it says "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources. Data source for Query1"

When I open the report I found the issue is from the 2 Power BI datasets, because data from the excel file are showing up correctly, but others are showing "can't display the visual: Couldn't load the data for this visual".

Is there a way to solve this issue? or if there is a better way to create and publish a custom usage metrics report with custom measures & data source?

Thank you!

[christinepayton]

You can't schedule refresh on PBI datasets because they are live - they don't need refresh. If I recall, though, the auto-generated usage reports in the service are something that you can edit (at one point I downloaded one of them to look at the DAX queries that were used out of curiousity), have you tried that? 

[Lexi_Liu]

Thanks Christine! I tried to edit in the service but no option for me to add my own data source (the target user list) in the metric report, so I created a new report in desktop, but then the data cannot display if I publish into Workspace.

[christinepayton · Accepted solution]

Yeah, you can't combine other sources with a PBI dataset. You can add measures etc though. If you want to truly customize, you can get the usage data out of the PBI Admin API and put it in a database with an ETL tool - it's handy to have that data anyway, it just takes considerable effort to get. 🙂

[Lexi_Liu]

Thank you! Now I understand 😃