Dynamic column renaming in the Parameter

[Alena_Zamolodch]

Hi All.

We need to create a table or matrix visual which will rename the columns, based on the selected slicers.

For example we have data value to show in one table:

And another table that stores Column names for each combination for each DQI_ID . For example:

As a result I must have a visual, that based on the selected DQI_ID shows table with values from the first table, but column names defined in the second table. For example, if DQI_ID=1.1 s selected than viz should look like this:

I started to implement this using the parameter.

Created the parameter with columns frm the first table and in the formula planned to change the names via DAX expression.

But the parameter does not recognizes the aplied slicers. For example in the code below, if I hardcode the DQI_ID I need it works. But if I try to refer to the current selection, it is not working properly, column names are not changing.

For example for the col1 renaming is not working. For col2 and col3 it is working. But I hardcoded there a DQI_ID. Ideally it must be changed based on the current selection:

Parameter =

  var dqi = [selected Dqi]

// var test = CONVERT(CALCULATE(SELECTEDVALUE('Bad records'[DQI_ID])),STRING)

 var col1 = calculate(max('Out mapping'[COLUMN_2]),'Out mapping'[DQI_ID]=dqi)

 var col2=calculate(max('Out mapping'[COLUMN_2]),'Out mapping'[DQI_ID]="4.2.2.1.11")

 var col3=calculate(max('Out mapping'[COLUMN_3]),'Out mapping'[DQI_ID]="4.2.2.1.11")

 Return

 {

    (col1, NAMEOF('Bad records'[COLUMN_1]), 0),

    (col2, NAMEOF('Bad records'[COLUMN_2]), 1),

    (col3, NAMEOF('Bad records'[COLUMN_3]), 2)

}

In the example I have only 3 DQI_ID but in reality there are hunderds of them with different set of the column names.

Do you have any ideas how to make it work?

[mark_endicott · Accepted solution]

@Alena_Zamolodch - Power BI doesn't automatically allow for dynamic renaming of columns in visuals, unlike Excel, and DAX is not the best way to work around this.

I would suggest creating a table with all potential column names and value combinations and using a slicer to filter the displayed columns based on user preferences.

Here are some videos that may be able to assist you

https://www.youtube.com/shorts/CHEneTrZav0 

https://www.youtube.com/watch?v=8U88ZRMsCI8&ab_channel=PowerBITambayan

If this helps, or works for you, please accept as the solutions to aid visibilty for others.