Forum Discussion
Dynamic table column name
Greg_Deckler Basically I want the column title in the table visual to be the results of a measure. That way the title will update as the value of the measure changes.
StaceyGriffeth In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563
If you can share data, can be more specific.
Greg_Deckler Here is the table:
The farthest right column, Cur, is the current month being reported (in this case June). -1 means current month less 1 (May), and so on. I have the following measures which set the current reporting month (updated monthly) and year (updated annually):
ReportMonth = 6ReportYear = 2020PriorYear = [ReportYear]-1I am trying to automate this as much as possible, so I do not want to have to manually change the table column titles every time the month is advanced. For example, this month, -1 is May, but next month -1 will be June. I have measures which set what month and year is -1, -2, etc based on ReportMonth and ReportYear:TTMReportMonth-1 = SWITCH([ReportMonth], 1, 12, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 7, 6, 8, 7, 9, 8, 10, 9, 11, 10, 12, 11)ReportShortMonthCurrent-1 = SWITCH([ReportMonth],2, "JAN", 3, "FEB", 4, "MAR", 5, "APR", 6, "MAY", 7, "JUN", 8, "JUL", 9, "AUG", 10, "SEP", 11, "OCT", 12, "NOV", 1, "DEC")TTMReportYear-1 = SWITCH([ReportMonth], 1, [PriorYear], 2, [ReportYear], 3, [ReportYear], 4, [ReportYear], 5, [ReportYear], 6, [ReportYear], 7, [ReportYear], 8, [ReportYear], 9, [ReportYear], 10, [ReportYear], 11, [ReportYear], 12, [ReportYear])The month and year for -1, -2. etc are combined in the following:TTM ReportMonthyear-1 = [ReportShortMonthCurrent-1]&" "&[TTMReportYear-1]And can be displayed in a card and placed above the table column as the column title:However, when data is refreshed with July as the current month, the column widths will change and the cards as titles will no longer line up with the table columns.StaceyGriffeth Right, so as Disconnected Table Trick explains you create a disconnected table with the values you want in the columns. You use the measure to get the column value MAX('DisconnectedTable'[Column]). You can then use this in your measure to return the correct calculation. It's kind of warped thinking at first but it works. It is sort of the reverse of what you are doing now using a measure to compute the column value you want. Think that you already know which column you are in.
These things are difficult to mock up specifically without sample source data as text to copy and paste.
In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563
Greg_Deckler I've been going over the disconnected table trick trying to figure out how it can solve my problem. I see how it would isolate just the data I want to include in my table, but how does it solve the issue I have regarding the column name in the table visualization? It seems like I would still have to update the column name each month to reflect the month/year it represents?