Dynamic column headers in matrix based on date slicer selection

[Anonymous]

Hi ,

I have a slicer with Year, Quarter, Monthname , Week,Day and based on the slicer selection the column headers should changed. I am able to make this happen by creating a table with just these and unpivoting these columns. But the dates are not sorting. 

Thanks 

[v-lid-msft]

Hi @Anonymous ,

We can create a calculated column as header filed to meet your requirement:

ColumnHeader = 
var yearTable = ADDCOLUMNS(SELECTCOLUMNS (
        GROUPBY ( 'Table', 'Table'[Date].[Year], 'Table'[Date] ),
        "Header", [Year],
        "Date", [Date]
    ),"Name","Year")
    var quarterTable = ADDCOLUMNS(SELECTCOLUMNS (
        GROUPBY ( 'Table', 'Table'[Date].[Quarter], 'Table'[Date] ),
        "Header", [Quarter],
        "Date", [Date]
    ),"Name","Quarter")
    var monthTable =  ADDCOLUMNS(SELECTCOLUMNS (
        GROUPBY ( 'Table', 'Table'[Date].[MonthNo], 'Table'[Date] ),
        "Header", [MonthNo],
        "Date", [Date]
    ),"Name","Month")
    var dayTable = ADDCOLUMNS(SELECTCOLUMNS (
        GROUPBY ( 'Table', 'Table'[Date].[Day], 'Table'[Date] ),
        "Header", [Day],
        "Date", [Date]
    ),"Name","Day")
var weekTable = FILTER(CROSSJOIN(GENERATESERIES(1,53),DISTINCT('Table'[Date])),WEEKNUM([Date])=[Value])
return
UNION (
    ADDCOLUMNS(yearTable,"Sort",RANKX(yearTable,[Header],,ASC,Dense),"NameSort",5),
    ADDCOLUMNS(quarterTable,"Sort",RANKX(quarterTable,[Header],,ASC,Dense),"NameSort",4),
    SELECTCOLUMNS(ADDCOLUMNS(monthTable,"Sort",RANKX(monthTable,[Header],,ASC,Dense)),"Header",Format([Date],"MMMM"),"Date",[Date],"Name",[Name],"Sort",[Sort],"NameSort",3),
    ADDCOLUMNS(dayTable,"Sort",RANKX(dayTable,[Header],,ASC,Dense),"NameSort",1),
    SELECTCOLUMNS(weekTable,"Header","WK "&[Value],"Date",[Date],"Name","Week","Sort",[Value],"NameSort",2)
)

"Header" Column is sorted by "Sort" column and the "Name" column is sorted by the "NameSort" column

If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that we have shared?

By the way, PBIX file as attached.

Best regards,

[Greg_Deckler]

Are they actually dates or text? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

You may need to create some kind of numeric Sort By column and flag your column to sort by this other column. Tough to say though.