How to get the latest sign-in data for each user w…

Scenario:
Use Power BI Desktop to connect to the data exported from AAD Portal to use DAX formula to get the latest sign-in data for each of the users within your tenant.

The specific operation is:

1. Log in to the AAD portal, search for Power BI’s login information based on your custom app and time range, and download it as a CSV.
2. Use Power BI Desktop to connect to the CSV file, import the data inside, and use DAX formula to get the latest sign-in data for each of the users within your tenant.

 

Sample data:

 

Expected result:
Get the latest sign-in data for each of the users within your tenant.

Detailed Steps:
Step 1:

Log in to the AAD portal, search for Power BI’s login information based on your custom app and time range, and download it as a CSV.
1) Sign in to the Azure Active Directory admin center, then select Azure Active Directory from the portal menu.
From the resource menu, select Monitoring > Sign-ins.

 

2) And you can search the sign-in info for Application “Microsoft Power BI” and custom select the time range you want to search for, then you can also try to download the searched result as CSV and investigate it in local side:

 

The official documentation is as follows:
https://docs.microsoft.com/power-bi/admin/service-admin-access-usage 

Step 2:

Use Power BI Desktop to connect to the CSV file, import the data and use DAX formula to get the latest sign-in data for each of the users within your tenant.
1) Install Power BI Desktop.
https://www.microsoft.com/en-us/download/details.aspx?id=58494 
2) You can go to the desktop and connect to the CSV file.

 

 

3) Use Dax to create a calculated column.

IsMaxdate =
VAR _maxdate =
    CALCULATE (
        MAX ( [Date (UTC)] ),
        FILTER (
            'InteractiveSignIns_2022-07-19_2022-07-26',
            [Username] = EARLIER ( [Username] )
        )
    )
RETURN
    IF ( [Date (UTC)] = _maxdate, 1, 0 )

 

4) Then you can try to create a table visual and place the columns [Date UTC] and [Username] in it. And place a visual filter to set the value of [IsMaxDate] to 1 to filter the table visual, then you can sort the visual by DateUTC ascending to find out what users’ latest sign-in time to your tenant’s Power BI and who are not using Power BI currently:

 

 

Summary:
Through the AAD portal and Power BI Desktop, get the latest sign-in data for each of the users within your tenant.
Hope this article can help you solve similar problems.

 

Author: Yang Liu

Reviewer: Ula Huang, Kerry Wang