Daily DAX : Day 300 USERNAME

Power BI DAX USERNAME Function

Description

The USERNAME function in Power BI DAX returns the username of the current user accessing the report or dashboard. It retrieves the User Principal Name (UPN), typically in the format of an email address (e.g., user@company.com), based on the user's Power BI login credentials.

Syntax

USERNAME()

Returns: A text string representing the current user's UPN.

Use Case

The USERNAME function is primarily used to implement row-level security (RLS) in Power BI, enabling personalized data filtering based on the logged-in user. It allows reports to dynamically display data relevant to the current user without requiring manual filtering.

Example Scenario

Suppose you have a sales dataset with a table named SalesData that includes a column UserEmail indicating which employee is responsible for each sale. You want to ensure that each employee only sees their own sales data in a Power BI report.

Implementation

1. Create a measure or calculated column using the USERNAME function:

   UserFilter = IF(SalesData[UserEmail] = USERNAME(), "Visible", "Hidden")

2. Apply row-level security in Power BI:
   • Go to Modeling > Manage Roles.
   • Create a role (e.g., "SalesPerson").
   • Add a DAX filter to the SalesData table: [UserEmail] = USERNAME().
3. Assign users to the role in the Power BI Service.

Result: When a user with the email john.doe@company.com accesses the report, they will only see rows in SalesData where UserEmail matches john.doe@company.com.

Notes

• USERNAME works in Power BI Service and Power BI Report Server but returns a default user in Power BI Desktop for testing (e.g., the local machine's username).
• Use with USERPRINCIPALNAME for consistency in Azure Active Directory environments, as USERNAME is equivalent to USERPRINCIPALNAME in most cases.
• Ensure the dataset's user column matches the UPN format returned by USERNAME.

Limitations

• Requires proper configuration of user identities in Power BI Service.
• Not suitable for static calculations, as it depends on the logged-in user.