cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MarkJames Member
Member

Usage Metric Report - create measure for distinct day and reports across tables

Hi,

 

I'm connecting to the Usage Metric Report in my workspace via Power BI Desktop - I've saved the Usage Metric Report after viewing the report in the Workspace which then let's me connect to it as a datasource in Power BI Desktop. I'm doing this because I want to add in some additional measures that help make the report more usable for me.

 

In my workspace I have 3 reports. If one use views all 3 reports in one day I want this to total 3, regardless of the number of times each report is viewed in that day. Then I want to be able to roll this up across days and users.

 

At the moment the 2 standard count metrics don't give me what I'm looking for:

 

  • ViewsCount - this counts the number of pages in a report that were viewed. I only want to count if the report was viewed, not all it's pages
  • ViewersCout - this only give me the unique number of users

 

I'm able to get the total distinct days viewed by user using the following:

 

Viewed = CALCULATE(DISTINCTCOUNT(Views[Date]),GROUPBY(Users,Users[UserGuid]))
 
But this only gives me the days. If they've viewed more than one report in that day it doesn't count this. Below I've given a screenshot of the table of data to help illustrate what I'm trying to achieve:
 
Usage Metric.PNG
In this example you'll see a column for Date (from Views table) and ReportGuid (from Views table) with the names of 3 Users (from Users table) in columns and a count of the times each report has been viewed, using the DAX calculation above. The totals are the bottom are only counting the unique days, not the unique days and report IDs. The real figures should be 1, 7 and 11.
 
Any suggestions?

MarkJames

5 REPLIES 5
Community Support Team
Community Support Team

Re: Usage Metric Report - create measure for distinct count across tables

Hi @MarkJames ,

If you mean the usage metrics report in Power BI Service, I'm afraid that we cannot get the dataset of that usage metrics report in Power BI Desktop or download that usage metrics report as a pbix by my test.

In addition, as we know that we cannot create measures or calculated column in Power BI Service, I'm afraid that we cannot achieve your requirement.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MarkJames Member
Member

Re: Usage Metric Report - create measure for distinct count across tables

Hi @v-piga-msft ,

 

I'm able to connect to the Usage Metric data for a Workspace by connecting to it as a Power BI dataset. I had to create the report in the Power BI Service first, then connect to the data. Once I've finished creating the report I'll remove the one on the Power BI Service and replace it with my PBIX file.

 

That aside, do you have any idea how I should go about getting the distinctcount I need of reports, viewers and days across 2 different tables?

 

Thanks,

MarkJames

Community Support Team
Community Support Team

Re: Usage Metric Report - create measure for distinct count across tables

Hi @MarkJames ,

If you have a live connection with Power BI dataset in Power BI Desktop, we only could create the measures.

Please share your data sample and your desired output so that we could help further on it.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MarkJames Member
Member

Re: Usage Metric Report - create measure for distinct count across tables

Hi @v-piga-msft ,

 

That's what I'm looking for, a measure that will help me get to my answer. I've attached an image that illustrates the issue ...

Usage Metric Report.PNG

On the first table I've taken the distinctcount of the Date field in the Views table to get the total days viewed for each person. But I need to count the number of individual reports they've visited each day. Power BI have set the ViewsCounnt metric to count the number of PAGES of a report someone views - so if I click on one page of one report that's one view. But if someone else clicks on one report and goes to all 6 tabs, their total views are 6, which is misleading.

 

The second chart uses the following calculation:

 

Viewed = CALCULATE(DISTINCTCOUNT(Views[Date]),GROUPBY(Users,Users[UserGuid]))
 
This gives me a more accurate number, as now I can see the unique daily visits for each person for each report.

 

The issue is the Users and Views tables are separate and I need a distinctcount of reports viewed by day by user.

 

Does that make sense?

MarkJames Member
Member

Re: Usage Metric Report - create measure for distinct count across tables

@v-piga-msft any thoughts on how I can do this?

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 131 members 1,704 guests
Please welcome our newest community members: