Change X-Axis title dynamically for different users as per derived data

[Anonymous]

Hello Folks,

Greetings..!!

Here I have 2 tables.

Mileage Details Table

USER_TABLE - In this table I have list of Column Names(Columns of Mileage Details table), User ID and Display Names.

Note - Display names are different user by user for the same Column name.

For ex. For User 1 ASSETTYPE is Asset Type 1 and for User 2 ASSETTYPE is Asset Type 2.

RLS is already applied for filtering data as per users Id.

These reports are embedded to the . net application and there are several users who are using that application.

User Requirement - A column chart should display bars for sum of mileage by Asset Type, sum of mileage by AuxData1, and so on. Here Asset Type, AuxData1, etc.  Should be provided as a choice in slicer. So when user selects let say Asset Type from the slicer then the chart should display different Asset Type on X-Axis and Sum of Mileage on Y-Axis.

Technical Specification - Now for displaying sum of Mileage filter by ASSETTYPE,AUXDATA1, and so on.. I have created a field parameter. Now added the Parameter in the X-Axis of clustered column chart and to the slicer.  

Ref image is as below.

Now I want to change the Title of X axis and Parameter display name as per the user’s login. For ex. If User 1 login to system, Title should be Asset Type 1 and if User 2 login to system, title should be Asset Type 2.

I have applied below formula for field parameter to achieve the requirement.
Parameter = {

   (CALCULATE(FIRSTNONBLANK(USER_TABLE[Display_Name],1),

    filter(all(USER_TABLE), "ASSETTYPE" = USER_TABLE[Column_Name])), NAMEOF('Mileage Details'[ASSETTYPE]), 0),

    (CALCULATE(FIRSTNONBLANK(USER_TABLE[Display_Name],1),

    filter(all(USER_TABLE),"AUXDATA1" = USER_TABLE[Column_Name])), NAMEOF('Mileage Details'[AUXDATA1]), 1),

    (CALCULATE(FIRSTNONBLANK(USER_TABLE[Display_Name],1),

    filter(all(USER_TABLE),"AUXDATA2" = USER_TABLE[Column_Name])), NAMEOF('Mileage Details'[AUXDATA2]), 2),

    (CALCULATE(FIRSTNONBLANK(USER_TABLE[Display_Name],1),

    filter(all(USER_TABLE),"CLIENTDATA1" = USER_TABLE[Column_Name])), NAMEOF('Mileage Details'[CLIENTDATA1]), 3),

    (CALCULATE(FIRSTNONBLANK(USER_TABLE[Display_Name],1),

    filter(all(USER_TABLE), "CLIENTDATA2" = USER_TABLE[Column_Name])), NAMEOF('Mileage Details'[CLIENTDATA2]), 4),

    (CALCULATE(FIRSTNONBLANK(USER_TABLE[Display_Name],1),

    filter(all(USER_TABLE),"DIVISION" = USER_TABLE[Column_Name])), NAMEOF('Mileage Details'[DIVISION]), 5),

    (CALCULATE(FIRSTNONBLANK(USER_TABLE[Display_Name],1),

    filter(all(USER_TABLE),"PREFIX" = USER_TABLE[Column_Name])), NAMEOF('Mileage Details'[PREFIX]), 6),

    (CALCULATE(FIRSTNONBLANK(USER_TABLE[Display_Name],1),

    filter(all(USER_TABLE),"Model Year" = USER_TABLE[Column_Name])), NAMEOF('Mileage Details'[Model Year]), 7)

}

Using this formula I am facing issue that, when I apply RLS it works for first user only and for other users in does not follow any consistency.

My suspect is, when we create any field parameter, it assigns value at design time and is not allowing to change value runtime. In this case, which changes needs to be applied to achieve this requirement? Any other suggestions which can fulfill the user requirement mentioned above are welcome.

Regards,

Dhwani

[123abc]

It seems like you want to dynamically change the X-axis title of a chart based on the user's login and their specific data attributes. Unfortunately, Power BI does not directly support dynamic chart title changes based on user attributes out of the box. However, there is a workaround you can consider to achieve this effect.

One approach is to use a combination of disconnected tables, slicers, and DAX measures to dynamically change the X-axis title based on user attributes. Here's a step-by-step guide:

1. Create a User Attribute Table: Create a table that maps user IDs to their respective attribute values. In your case, this would map user IDs to the display names for attributes like Asset Type, AuxData1, etc.

2. Create a Slicer: Add a slicer to your report that allows users to select their desired attribute (e.g., Asset Type, AuxData1).

3. Create a Measure for X-Axis Title: Create a DAX measure that dynamically calculates the X-axis title based on the selected slicer value. Here's an example measure:

   DAXCopy code

   XAxisTitle = VAR SelectedAttribute = SELECTEDVALUE('SlicerTable'[SelectedAttribute]) VAR UserAttribute = LOOKUPVALUE('UserAttributeTable'[Attribute], 'UserAttributeTable'[User ID], USERNAME()) RETURN IF(ISBLANK(SelectedAttribute), "", IF(UserAttribute = SelectedAttribute, UserAttribute, ""))

   Replace 'SlicerTable' with the name of your slicer table, 'UserAttributeTable' with the name of your user attribute table, and 'User ID' and 'Attribute' with the appropriate column names.

4. Add X-Axis Title Measure to the Chart: Remove the field parameter from the X-axis of your chart and replace it with the XAxisTitle measure.

5. Apply RLS and User Authentication: Ensure that Row-Level Security (RLS) is correctly applied to your data model to restrict data access based on user roles. Additionally, make sure that user authentication is properly configured in your Power BI environment.

6. Test the Solution: Test the solution with different user logins to verify that the X-axis title dynamically changes based on the user's selected attribute.

This approach leverages the slicer to allow users to select their desired attribute and the DAX measure to dynamically calculate the X-axis title based on the selected attribute and the user's login. It should provide the flexibility you need to meet your user's requirements.

Please adapt the table and column names in the DAX measure to match your specific data model structure.

XAxisTitle = VAR SelectedAttribute = SELECTEDVALUE('SlicerTable'[SelectedAttribute]) VAR UserAttribute = LOOKUPVALUE('UserAttributeTable'[Attribute], 'UserAttributeTable'[User ID], USERNAME()) RETURN IF(ISBLANK(SelectedAttribute), "", IF(UserAttribute = SelectedAttribute, UserAttribute, ""))

1. Replace 'SlicerTable' with the name of your slicer table, 'UserAttributeTable' with the name of your user attribute table, and 'User ID' and 'Attribute' with the appropriate column names.

2. Add X-Axis Title Measure to the Chart: Remove the field parameter from the X-axis of your chart and replace it with the XAxisTitle measure.

3. Apply RLS and User Authentication: Ensure that Row-Level Security (RLS) is correctly applied to your data model to restrict data access based on user roles. Additionally, make sure that user authentication is properly configured in your Power BI environment.

4. Test the Solution: Test the solution with different user logins to verify that the X-axis title dynamically changes based on the user's selected attribute.

This approach leverages the slicer to allow users to select their desired attribute and the DAX measure to dynamically calculate the X-axis title based on the selected attribute and the user's login. It should provide the flexibility you need to meet your user's requirements.

Please adapt the table and column names in the DAX measure to match your specific data model structure.

[Anonymous]

Hi @123abc 

Thank you for your response and help. I tried to implement your solution. I am still stuck at the point where I need to map my parent table column names with Mapping Table data. 
Let me brief, AssetType is column name in my parent table(Mileage Details Table) and AssetType is data for my child Table(User_Table) where I am mapping the display name of column name for different users.
I tried to implement XAxisTitle formula in various ways but,
1. If I create measure I cannot place in X-Axis and
2. If I create calculated column, the formula is not working. so my chart is also not working.   
When I tried the exact same formula which you mentioned it is giving me below error

If you can brief some more ideas I can definitely check for that.

Thank you again!!

Dhwani 🙂

[MFelix]

Hi @Anonymous,

Check if this can help you

https://www.youtube.com/watch?v=NqlgTXCfqSs

This is for measures but can also work for axis.