cancel
Showing results for 
Search instead for 
Did you mean: 
v-henryk-mstf

How to get the latest sign-in data for each user within your tenant

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:

vhenrykmstf_0-1660901801387.png

 

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.

vhenrykmstf_1-1660901988956.png

 

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:

vhenrykmstf_2-1660902012782.png

vhenrykmstf_3-1660902022316.png

 

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.

 

vhenrykmstf_4-1660902054830.png

 

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 )

vhenrykmstf_5-1660902101027.png

 

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:

 

vhenrykmstf_6-1660902124425.png

 

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

 

Comments
Polls
What is your favorite Power BI feature release for November 2022?