Forum Discussion

DAX_merchant's avatar
DAX_merchant
Helper II
1 year ago
Solved

Sorting a matrix with multiple measures by column

I have a matrix with columns as years, rows as stores and two measures populating each column: employees and total sales. 

 

I want to be able to sort the rows of the matrix by any year, not only the total.

 

The total column in my matrix has actually been repurposed using HASONEVALUE to return a different value for sales (sales delta between the latest two time periods) and the employees "total" returns a rank conditional on a disconnected table showing all time periods, which I then made a slicer with, and using selected value can calculate the rank of each store given the slicer selection using SELECTEDVALUE. 

 

Now the problem is that I want the slicer to be overlayed on the columns, but this does not work since there is a region slicer on the page which will drastically change the size of the sales numbers in the matrix, meaning the overlay only fits sometimes (auto shrinking columns)

 

My question: Regardless of that slicer, when I click the column header (year) it highlights that column and greys out the rest. Is there a way to extract the value that I am currently clicking? It doesn't seem to work since the totals column will never have context on date and so selected value won't work there. 

 

Ideally, I click one of the years, and then this value can somehow be called within a measure. 

 

Need Help
  • v-echaithra's avatar
    v-echaithra
    1 year ago

    Hi DAX_merchant ,

    Thanks for the update. Unfortunately, Power BI does not currently support fixed or persistent column widths in matrix visuals. The column width is automatically calculated based on the data in each column and is recalculated any time the visual is refreshed or slicers are changed. There is no built-in setting to lock or fix column widths.
    I tried to recreate the issue on my end, and it worked fine. This behavior is likely unintended or a setting that hasn’t been fully integrated into Power BI Desktop yet.
    If this feature is important to you, consider submitting a suggestion on Fabric Ideas or voting for an existing one.
    https://community.fabric.microsoft.com/t5/Fabric-Ideas/idb-p/fbc_ideas

    If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

    Regards,
    Chaithra.



8 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    1 year ago

    In Power BI, you cannot directly extract the clicked column header from a matrix visual (since it doesn’t pass filter context like a slicer does).

    Create a table that contains all the years in your dataset and you will use this as a single-select slicer so the user can choose a sorting year.

    YearSelection = DISTINCT('YourDateTable'[Year])

    Then you need a measure to extract the selected year from the slicer:

    SelectedYear = SELECTEDVALUE(YearSelection[Year], YEAR(TODAY()))

    And another measure for ranking based on the selected year based on total sales for the selected year :

    RankByYear =
VAR _Year = [SelectedYear]
RETURN 
    RANKX(
        ALLSELECTED('Stores'),
        CALCULATE(SUM('Sales'[Total Sales]), 'Date'[Year] = _Year),
        ,
        DESC,
        DENSE
    )

    Another alternative is using  Bookmarks & Buttons to simulate clicking on a year.

    • DAX_merchant's avatar
      DAX_merchant
      Helper II
      1 year ago

      Unfortunate that you can't do that. 

       

      That solution I already implemented, but I guess it is not possible to elegantly overlay that slicer onto the column headers. 

       

      Thanks.

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

    Hi DAX_merchant ,

    Thank you for reaching out to Microsoft Community.

    You can’t extract the clicked column header from a matrix.

    Using buttons, bookmarks, or rethinking the visual layout can be a workaround.

     

    Replace the slicer with a set of buttons and bookmarks:

     

    Create buttons for each year.

    Each button selects a year via slicer sync or bookmarks with slicer pre-selected.

    Style them to look like clickable headers (stacked horizontally above the matrix).

     

    This doesn’t solve column highlighting but provides a cleaner, fixed layout.

    If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

    Regards,
    Chaithra




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

    Hi DAX_merchant ,

    We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
    If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

     

    Regards,
    Chaithra.

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

    Hi DAX_merchant ,

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
    If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

     

    Regards,
    Chaithra E

    • DAX_merchant's avatar
      DAX_merchant
      Helper II
      1 year ago

      Hello,

      Apologies for the late response. The solution you gave is not satisfactory as my date ranges can change depending on the time of year the report is viewed. Besides, it has the same issue as the current method that I already described in my innitial question which is that the button size cant be dynamic while I know that my column widths are, depending on the size of the region I am slicing on.

       

      It is possible that I am misunderstanding your solution but having buttons is more difficult than having a slicer if the number of choices is dynamic (with a slicer at least, I know that when my report is refreshed, the disconnected table I made will update while the bookmarks will have to be done manually, to my understanding...)

      EDIT: Perhaps a more appropriate question would be: is it possible for force a certain column width in a matrix that will persist when the visual is refreshed (changing slicer selection or report refresh)?

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

    Hi DAX_merchant ,

    Thanks for the update. Unfortunately, Power BI does not currently support fixed or persistent column widths in matrix visuals. The column width is automatically calculated based on the data in each column and is recalculated any time the visual is refreshed or slicers are changed. There is no built-in setting to lock or fix column widths.
    I tried to recreate the issue on my end, and it worked fine. This behavior is likely unintended or a setting that hasn’t been fully integrated into Power BI Desktop yet.
    If this feature is important to you, consider submitting a suggestion on Fabric Ideas or voting for an existing one.
    https://community.fabric.microsoft.com/t5/Fabric-Ideas/idb-p/fbc_ideas

    If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

    Regards,
    Chaithra.



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

    HI DAX_merchant ,

    As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

    If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
    If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Chaithra E.