Forum Discussion

kkanukurthi's avatar
kkanukurthi
Helper III
1 year ago
Solved

Unable to sort Matrix visual column values while clicking on column headers

Hi All, Need help on sorting Matrix column values ascending or descending. Values calculated by measure.

Scenario

I have a matrix visual in a Power BI report:

  • Rows: "Category" column from the Orders table (A,B,C,D,E etc).
  • Values: A measure that Picks icon and Day values from the Ship Date column from Orders table.
  • Columns: A field from a Calender table that contains Months and Years as text column sorted by yearmonth code(formatted as "mmm yyyy", e.g., Apr 2025, Mar 2025).

 

Measure used in values:

Date_Format_Sign =
var month_year = SELECTEDVALUE('Calendar'[Start of Month])
var mindate = CALCULATE(min(Orders[Ship date]),ALLSELECTED('Calendar'))
var minday = day(mindate)
var mindatestartofmonth = date(year(mindate),month(mindate),1)
Return
IF(mindatestartofmonth=month_year,
switch (TRUE(),
minday >=1 && minday<7,UNICHAR(9650)&minday,
minday >=7 && minday<14,rept(UNICHAR(160),4)&UNICHAR(9650)&minday,
minday >=14 && minday<=21,rept(UNICHAR(160),8)&UNICHAR(9650)&minday,
REPT(UNICHAR(160), 12)&UNICHAR(9650)&minday), BLANK())

 

 

Expected Output-Month Year column values to be sorted out either in Ascending or descending when we click on column headers

 Please help! Thanks in Advance

 

 

Need Help
Show and Tell
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi kkanukurthi ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    In Table visual, It is not possbile to represent  Month-Year placed horizontally with categories in rows. And in Matrix visual, it is not possible  to sort the values under column headers.

     


    If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
    Thanks and Regards

9 Replies

  • kkanukurthi's avatar
    kkanukurthi
    Helper III
    1 year ago

    Hi All, Need help on sorting Matrix column values ascending or descending. Values calculated by measure.

    Scenario

    I have a matrix visual in a Power BI report:

    • Rows: "Category" column from the Orders table (A,B,C,D,E etc).
    • Values: A measure that Picks icon and Day values from the Ship Date column from Orders table.
    • Columns: A field from a Calender table that contains Months and Years as text column sorted by yearmonth code(formatted as "mmm yyyy", e.g., Apr 2025, Mar 2025).

     

    Measure used in values:

    Date_Format_Sign =
    var month_year = SELECTEDVALUE('Calendar'[Start of Month])
    var mindate = CALCULATE(min(Orders[Ship date]),ALLSELECTED('Calendar'))
    var minday = day(mindate)
    var mindatestartofmonth = date(year(mindate),month(mindate),1)
    Return
    IF(mindatestartofmonth=month_year,
    switch (TRUE(),
    minday >=1 && minday<7,UNICHAR(9650)&minday,
    minday >=7 && minday<14,rept(UNICHAR(160),4)&UNICHAR(9650)&minday,
    minday >=14 && minday<=21,rept(UNICHAR(160),8)&UNICHAR(9650)&minday,
    REPT(UNICHAR(160), 12)&UNICHAR(9650)&minday), BLANK())

     

     

    Expected Output-Month Year column values to be sorted out either in Ascending or descending when we click on column headers

     Please help! Thanks in Advance

     

     

  • v-dineshya's avatar
    v-dineshya
    Community Support
    1 year ago

    Hi kkanukurthi ,

    Thanks for reaching out to the Microsoft fabric community forum. 

     

    To sort the matrix columns (Month-Year) correctly (even with your measure), follow these steps:

    Step 1: Add a numeric or date field to Calendar
    Ensure your Calendar table has a numeric sort column, like YearMonthCode or an actual Date.
    Example:
    Month-Year Start of Month YearMonthCode
    Mar 2025 01-Mar-2025 202503
    Apr 2025 01-Apr-2025 202504

    Step 2: Set Sorting in Data View
    In the Calendar table: Click on the Month-Year column (the one you use in Columns). Go to Column tools → Sort by Column → choose YearMonthCode. This ensures the visual knows how to sort the columns properly, even if you're showing a measure in the values.

    In Matrix visuals, column header sorting works only when:
    The column is a text or date field from the data model. The Values section doesn't contain a complex or custom measure that affects sorting.

    In your scenario:
    The measure Date_Format_Sign is text-based (icons + spacing). It's used in the Values, so Power BI can't use it for sorting columns. The column field (Calendar[Month-Year]) is likely a text field, not a sortable date or number. Hence, click-to-sort on column headers doesn't work in this setup.

     

    If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
    Thanks and Regards

     

     

    • kkanukurthi's avatar
      kkanukurthi
      Helper III
      1 year ago

      Hi v-dineshya , Thanks for the reply. the solution above provided is for sorting column headers, not for values under columns. So you mean to say we cant sort values ascending or decending under column headers. Can you please tell me alternative approach so that i need sorting of values under same matrix visual. please show and tell with pbix file if possible the same expected output.

       

      • Rows: "Category" column from the Orders table (A,B,C,D,E etc).
      • Values: A measure that Picks icon and Day values from the Ship Date column from Orders table.
      • Columns: A field from a Calender table that contains Months and Years as text column sorted by yearmonth code(formatted as "mmm yyyy", e.g., Apr 2025, Mar 2025)           

      Expected Output: Ability to sort values  either in Ascending or descending when we click on column headers

       

      Thanks. Please help!

      • v-dineshya's avatar
        v-dineshya
        Community Support
        1 year ago

        Hi kkanukurthi ,

        Thanks for reaching out to the Microsoft fabric community forum. 

         

        In Matrix visual, it is not possible  to sort the values under column headers. You can acheive your requirement in Table visual. Please replace Matrix visual with Table visual.

         

        Please refer the sample pbix file.

         

        If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
        Thanks and Regards