Microsoft 365 Usage Analytics - Question

[Hans101]

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 many licenses are currently active under a specific UPN after I extract the Domain. Im also using the Date Hierarchy for Year/Month as well.

Is there a way to make this happen with the current Dataset/Semantic Model for Microsoft 365 Usage Analytics?

Thanks in adavnce!!

[Hans101]

Hi AmiraBedh,

Thank you for the response however I should have added more content. Im not having issues creating the report as this is of course the easy part.  😉

The current issue Im having is the 'TenantOfficeLicenses' [TimeFrame] date that is linked to 'Calendar' [Date] seems to be broken. With the Domain extracted, you can make a selection in the filter but the data in the table doesnt change.

↓ 'UserState' [TimeFrame] is also only showing one date, which I find to be strange. ↓

Side Note: The Microsoft 365 Usage Analytics (Semantic Model) has had no changes made to it.

Any suggestions?

[AmiraBedh]

I found these links they may help :

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

https://community.fabric.microsoft.com/t5/Desktop/Microsoft-365-Usage-Analytics-user-assigned-licens...

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

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 🙂