I want dynamic column headers on a matrix based on measure

[YukonCornelius]

Hi to all.  I have seen a number of asks on this forum that are very close to what I'm asking.

If I've missed one that is what I need I apologize.

Thank you to one and all.

The dashboard: It has a dropdown for the user to pick the first in a series of years they want to evaluate all assets' ages, depreciation, and other age related measures against.  For my example here I have limited the other years columns to three but the reality is there will be a hundred years.

Here's the dashboard: Dashboard 

It's because the dropdown choice dictates the year that all assets will be evaluated against puzzles me.  If I only had to compare the dropdown choice to each asset's in service year (that is, something in the asset's row of info) then this would worked about 4 hours ago.

My problem: I set up a parameter and then variables so that I could dynamically change column headers from Y_Position1, Y_Position2 and so on to whatever the first year chosen is, then the year after that and so on.

I'm thinking my schema is the problem but because the variable year to evaluate all the assets against is not mapable to each asset's record I'm probably stuck.

My attempt:

I have followed steps in one tutorial to create a parameter with one variable for each wanted-to-be-dynamic header.

Based on the I would have thought that the Y_ShowYear1 would have shown the [Year Chosen] from the slicer.  It just ignores the assignment I give it.  I tried var Y_ShowYear1 = 2022 and the column header Y_Position1 still didn't change.

I tried using the slicer that the parameter offered but couldn't get it to dynamically change the column headers either.

Thanks again.

 

[YukonCornelius]

Hi @twi1  and @Anonymous .  I'm really past my limit of ability here so I thank you for your insights and knowledge that given my situation it wasn't doable.  In the end, the software we were reading data from had a forecasting tool we could work with and export.  My years columns were able to come in from the source after a bit of effort there.  I look forward to this functionality in Power BI.  🙂

[YukonCornelius]

Hi @twi1  and @Anonymous.  Thank you for your thoughts.

Yes, there is a slicer with a calculation resulting from it that then influences the column header names.

I'm just going to conditionally colour columns in the matrix and look forward to the feature one day.

All the best,

David

[Anonymous · Accepted solution]

Hi @YukonCornelius

You can use field parameters but if there is slicer and calculation is based on slicer then it won't work.
Refer to the following post:Solved: Dynamic Matrix Column Header - Microsoft Fabric Community

You can propose an idea about this, you can submit your idea by going to the link below:
 Home (microsoft.com)

Best Regards, 

Jayleny 

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly. 

[twi1]

I have tried another ways but it can not help, it places dynamic data on values and i can not place them on column , I am sorry if I explore something new regarding this, I will definetely write here,
I am sorry again, 
Best regards

[YukonCornelius]

Hi @twi1.  Thank you for your thoughts and help!

Evidently I can't write anything succinctly.  I still couldn't get the matrix headers to dynamically be changed based on the selected value.

I did the steps 1-3 but I think what my problem is is that I cannot link the years parameter table to the main data table.  Yes, I can find a years field in the main data table but it serves a different purpose compared to the years list table.

The main data table has one years field and it is for the date the asset was created.

The years parameter table is to be used to pick one year that would be used to evaluate how old the asset is, is it due for replacement, what its depreciation should be as of that year.

So if I pick 2024 and there was just one asset in the main table with a created date of 1999 and it is due to be replaced every 24 years, I will get metrics telling me it's 25 years old, is overdue, etc..  Those metrics are working fine for the values section of the matrix.  Unfortunately I don't think I would/could link the tables based on the year variable.  That's the only year variable there is in the main table.  Hope that makes sense.

Does the Years table have to be linked to the main table to create a dynamic header for this main table visual?

Here is the dashboard with your steps entered in it.  I've used your variable names: Dashboard with TWI1's steps 

[twi1]

Hi @YukonCornelius  I think it will help you set up dynamic column headers:

 Parameter for Year Selection: Ensure you have a parameter table that lists the years you want to analyze. This should be linked to your main data.

 Measure for Dynamic Headers: Create measures that determine the header names based on the selected year.

 Matrix Setup: Use these measures in your matrix to dynamically update the column headers.

 Step 1: Create a Year Parameter Table
 Create a table for years, if not already created:
Years_List = GENERATESERIES(2000, 2100, 1)

 Step 2: Create Measures for Dynamic Headers
 Selected Year: Create a measure to capture the selected year from the slicer.
SelectedYear = SELECTEDVALUE('Years List'[Year])

 Dynamic Header Measures:
YearColumn1 = "Year " & [SelectedYear]
YearColumn2 = "Year " & ([SelectedYear] + 1)
YearColumn3 = "Year " & ([SelectedYear] + 2)
YearColumn4 = "Year " & ([SelectedYear] + 3)

 Step 3: Implementing in the Matrix
 Use the measures for headers in your matrix:
 In the columns section of your matrix visual, place these dynamic header measures.
 Ensure your data model supports these dynamic headers by linking the parameter table correctly.

 Example Implementation:
 DAX for Dynamic Headers:
YearColumnHeaders =
VAR Y_ShowYear1 = [SelectedYear]
VAR Y_ShowYear2 = [SelectedYear] + 1
VAR Y_ShowYear3 = [SelectedYear] + 2
VAR Y_ShowYear4 = [SelectedYear] + 3
RETURN
{
(Y_ShowYear1, NAMEOF([Y_Position1])),
(Y_ShowYear2, NAMEOF([Y_Position2])),
(Y_ShowYear3, NAMEOF([Y_Position3])),
(Y_ShowYear4, NAMEOF([Y_Position4]))
}

Setting Relationships:
Make sure your relationships between the Years List and your main data table are correctly set, as shown in the second image you uploaded.

Troubleshooting:
Verify Slicer Selection: Ensure the slicer is correctly filtering the data based on the selected year.
 Check Relationships: Make sure the relationships between tables are active and correctly set to filter the data.
 Matrix Settings: Verify that the matrix visual is correctly set to use the dynamic header measures.

 If you are using complex data structures or additional transformations, ensure that all related tables are appropriately updated.
 Test with a smaller dataset to ensure the dynamic headers are working before scaling to larger datasets.
 If you encounter any specific errors or issues, please provide more details, and I'll be happy to assist further!

[twi1]

Hi @YukonCornelius , Tell me if this response helps you a bit. I am here for further questions and help anytime. you can use the DAX UNION function in combination with a measure to dynamically change the column headers based on the user's selection.

Steps to Create Dynamic Column Headers:

1. Create a Parameter for Year Selection: Ensure you have a parameter slicer where the user can select the starting year for analysis.

2. Create a Measure for Dynamic Column Headers: Use DAX to create a measure that calculates the column headers dynamically based on the selected year.

Example DAX Measure for Dynamic Column Headers:

Let's assume you have a parameter called [Year Chosen] and you want to create dynamic column headers for the next 4 years.
YearColumnHeaders =
VAR Y_ShowYear1 = [Year Chosen]
VAR Y_ShowYear2 = [Year Chosen] + 1
VAR Y_ShowYear3 = [Year Chosen] + 2
VAR Y_ShowYear4 = [Year Chosen] + 3
RETURN
UNION(
ROW("Year", Y_ShowYear1, "Position", "Y_Position1"),
ROW("Year", Y_ShowYear2, "Position", "Y_Position2"),
ROW("Year", Y_ShowYear3, "Position", "Y_Position3"),
ROW("Year", Y_ShowYear4, "Position", "Y_Position4")
)
Create a Table for Dynamic Headers: Create a new table in Power BI to store the dynamic headers.
DynamicHeaders =
UNION(
SELECTCOLUMNS(
CALCULATETABLE(
GENERATESERIES(
[Year Chosen], [Year Chosen] + 3, 1
)
),
"Year", [Value],
"Position", SWITCH([Value] - [Year Chosen] + 1,
1, "Y_Position1",
2, "Y_Position2",
3, "Y_Position3",
4, "Y_Position4"
)
)
)

• Use the Measure in Your Matrix: Drag the DynamicHeaders table into your matrix's columns, and use the Year field from this table as your column headers.

• Create a Measure to Display Values: Create a measure that calculates and displays the values dynamically based on the year and position.
  DynamicValues =
  SWITCH(
  TRUE(),
  SELECTEDVALUE(DynamicHeaders[Position]) = "Y_Position1", [Y_Position1],
  SELECTEDVALUE(DynamicHeaders[Position]) = "Y_Position2", [Y_Position2],
  SELECTEDVALUE(DynamicHeaders[Position]) = "Y_Position3", [Y_Position3],
  SELECTEDVALUE(DynamicHeaders[Position]) = "Y_Position4", [Y_Position4]
  )
  Add the Measure to Your Matrix: Add the DynamicValues measure to the values section of your matrix.
  

  • DynamicHeaders Table: This table dynamically generates rows for each of the four positions based on the selected year. The SELECTCOLUMNS function is used to create columns "Year" and "Position".
  • DynamicValues Measure: This measure uses the SWITCH function to determine which position's value to display based on the selected column header.

  This approach should dynamically update your matrix column headers and values based on the year selected by the user. Adjust the logic as needed based on your specific data model and requirements.

[YukonCornelius]

Hi @twi1.  Interesting.  At this point I'll need a few days to come back to this and try it out.  Will advise this thread how it goes.  Thank you.