Measure Creation - Need to combine static data (at a certain level) with dynamic measure

[SahityaYeruband]

Hi All,

I have a requirement to create a visual, which has to show how many times a report was accessed from ReportServer DB.
For Subscription reports, the usage was coming as 0 as no user accesses the url for it to get captured in the reportserver DB.
However, I have the approx monthly frequency of the subscription report & the count of users to whom the mail will be sent. This information is present in a dataset - 

Subs_usrcnt

Path

User_Count

Delivery Extention

I have a fct dataset created after multiple appends etc, columns of interest are listed below - 

Final Dataset

Country

Department

Report Name

Item_Path

Monthly_Frequency

User_Name

Time_Start (time when report was accessed)

The tables are joined using - Path field.  1:M (Subscription - Final Dataset).

I have a date table, joined with Final Dataset on the time_start field.

Dashboard should be showing data -monthly.

For me to get the Monthly Report Hits (number of times the report is accessed) : 
For Subscription Reports - [count of user by report(if any) from the Final Dataset] + [(Monthly Freq * user_count) for that report]
at a monthly level the 1st part is dynamic & the 2nd part is constant - should not get aggregated or anything at a report name level.

For other reports (having pbix file) - Count of user accessed the report in that month.

1 measure for all the reports. 

And then somehow I have to keep the same number - [(Monthly Freq * user_count) for that report] across all months, but it should get rolled up when country & dept alone are selected.

The dashboard has 2 visuals - 
1. Country and dept wise - report hits (along with a few other measures)
2. Country, Dept, Report Name & Month wise - Report Hits

Appreciate your help in advance.

Thanks,
Sahitya Y

[d_gosbell · Accepted solution]

I think your data model is not right, if you split out the report and user data into their own dimensions then you can simply sum the monthly frequency from the Final Dataset and the sum the UserCount from the subscriptions and add these 2 measures together.

eg

Report

Report Name

Item_Path

User

Country

Department

User_Name

Final Dataset

Item_Path

Monthly_Frequency

User_Name

Time_Start (time when report was accessed)

Subscription

Path
User_Count
Delivery Extention

Then create the following relationships:

Report 1 -> M  Subscription

Report 1 -> M Final Dataset

User 1 -> M Final Dataset

[SahityaYeruband]

Hi @d_gosbell, I changed the data model, and it was much easier with dealing with the measures I wanted to create with this new model.