Aimplan User Key configuration



The Aimplan custom visuals require you to provide a 'measure' to the value field 'Aimplan User Key'.



The objective of this field is to provide information to the custom visual who the user is that is currently working with the custom visual. This information is used when storing data in the Aimplan database to track which user has changed what data (audit trail).

Power BI implements a function called USERPRINCIPALNAME(). When running a report in Power BI service, this function returns the User Principal Name (UPN), which normally is the user's e-mail address. This is the function that is normally used when implementing so-called row-level security to control data access across a Power BI model (you can read more about RLS here https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-rls).

You can try in Power BI to create a measure like this: UserPrincipalName = USERPRINCIPALNAME() , and show this measure in a 'Card' visual and you will see your current UPN. When running this in Power BI service you will see your e-mail address. But when you show this measure in Power BI desktop you will see your e-mail address or your domain\username.

But how is this related to the 'Aimplan User Key' value field? In your Power BI models using Aimplan visuals you should add the DimUser table from the Aimplan database and rename it to 'User'. This table must contain all users that should be able to use the Aimplan custom visuals. This table contains two important fields; a UserKey and the UserPrincipalName for each user. The UserPrincipalName is the registered e-mail address added when you added users to the Aimplan admin portal.

We should now create a measure in the model, that you can drag-and-drop to the 'Aimplan User Key' value-field for the visual. The measure should use the USERPRINIPALNAME() function (i.e. the current user e-mail address) to get the 'UserKey' Id number for the current user.

Create a new calculated measure using the DAX below.

You can create this calculated measure in the User (DimUser) table.

If you add this measure to a 'Card' visual you should see a number. This number is your unique UserKey or UserId in Aimplan. This is the same Id as you will see for each user in the admin portal when managing users.

The above measure is what you should drag-and-drop to the 'Aimplan User Key' value-field.





Adding a measure from any unrelated table to a Power BI visual with data from another table will cause Power BI to perform a cross-join returning all combinations of the dimension members. Say for example that you have last year SalesAmount on four different Product Subcategories, and these are the subcategories for which you want to plan / edit data (Plan Amount). Something like this:

Adding the 'CurrentUserKey' to the 'Aimplan User Key' value-field will make Power BI to do a cross-join and expand all the members of the product subcategories dimension and you will end up with something like this:



When designing the visual, you can in design mode see the 'number of rows' returned by Power BI to the visual. When you add an unrelated measure such as the CurrentUserKey to the 'Aimplan User Key' value-field you will see the number of rows grows exponentially.

Sometimes this is exactly what you want to achieve. You want the user to be able to forecast all product categories, not only the product categories with sales from last year or similar. But you should carefully consider how you decide to "push" our rows to the users.

But for performance reasons, you want to limit the number of data rows returned by Power BI to the visual as much as possible. The best way of doing this is to analyze what other measures will be used for a specific visual and create many different versions of the CurrentUserKey, and instead use such Key in each visual.



When using the Power BI Desktop, the USERPRINCIPALNAME() will as described above not return your e-mail address. This implies that the CurrentUserKey above will return null/blank, because there will be no match with your e-mail address in the User table.

There are two workarounds that you can use in Power BI desktop. The easiest alternative is just hard-coding your UserKey into the measure.

The second alternative is to 'prepare a measure' to be used in Power BI service, but hard-code the e-mail address like this. Later you can just replace your hardcoded email with =USERPRINCIPALNAME()