Forum Discussion

Hans101's avatar
Hans101
Regular Visitor
1 year ago

Microsoft 365 Usage Analytics - Question

With multiple companies under one hood, I would like to use 'TenantOfficeLicenses' [assigned count] with 'TenantOfficeLicenses' [LicenseName] along with 'UserState' [UPN] for a filter to show how man...
Need Help
AmiraBedh's avatar
AmiraBedh
Super User
1 year ago

I found these links they may help :

https://community.fabric.microsoft.com/t5/Power-Query/MS365-detailed-user-license-report/td-p/1150334

https://community.fabric.microsoft.com/t5/Desktop/Microsoft-365-Usage-Analytics-user-assigned-licenses/td-p/2570019

https://learn.microsoft.com/en-us/microsoft-365/admin/usage-analytics/usage-analytics?view=o365-worldwide

My idea to  filter based on the domain of the UPN, you can create a CC in your 'UserState' table to extract the domain from the UPN :

Domain = RIGHT('UserState'[UPN], LEN('UserState'[UPN]) - FIND("@", 'UserState'[UPN]))

 

You can use the 'TenantOfficeLicenses'[AssignedCount] and 'TenantOfficeLicenses'[LicenseName] to show how many licenses are currently active for each user (UPN) filtered by the domain :

ActiveLicenses = 
CALCULATE(
    SUM('TenantOfficeLicenses'[AssignedCount]),
    FILTER('TenantOfficeLicenses', 'TenantOfficeLicenses'[AssignedCount] > 0)
)

 

This is an old code I had, you may need to adjust it for your needs 🙂