cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Sorting text in a matrix

Hello,


Working on a project to show history of senior residents in a facility. 

The desired visual is a matrix that shows the Resident, Tenant Status, and Care Level Status.

There is no corelation between a tenant status change and a care level status change, they can happen independant of each other.

 

To build this I took columns from the Tenant Status then did a lookup to the Care Level Status during that period.

This all returns well and good but when I attempt to display the data in a matrix the text columns become last/first and therefor sort by a-z/z-a instead of by last date.

image.png

 

So, I went to my table and tried to set the sort by columns but nothing seems to work.  Anythign I try to sort by I get the cannot have more than one value per sort value type error.  I tried adding an index to sort by, same error.

 

Data looks like this:

image.png

 

What do I need to do to be able to get these top level values in the matrix to match the latest date value instead of the a-z sort that's happening?

 

Thanks!

10 REPLIES 10
Highlighted
Super User IV
Super User IV

Re: Sorting text in a matrix

@ctaylor , This is the way out. Create a desc rank on your date (say date is the column you want to sort desc)

Prefer to make copy of the column you want to do it

 

Date1 = 'Date'[Date]

Date Rank =  RANKX(ALL('Date'),'Date'[Date],,DESC,Dense)

 

Mark Date Rank as the sort column of Date1 or Date as per need. No that date will by default sort desc

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

Re: Sorting text in a matrix

@amitchandak 

Let me see if I understand you correctly.

image.png

 

Create Date1 by copying [From Date]

Then rank the [From Date]:

Date Rank = RANKX(ALL('Status and Care History'),'Status and Care History'[From Date].[Date],,DESC, Dense)

Then sort Date1 by the Date Rank.

image.png

Then use [Date1] on my matrix and sort by [Date1].

 

That doesn't change anything unfortunately.  If I try to set the sortby for the two text columns of [Resident Status] and [Care Level] I still get the cannot sort error when I try to sort by either [Date Rank] or [Date1]

 

So, I am still confused

Highlighted
Helper III
Helper III

Re: Sorting text in a matrix

@amitchandak 

I also tried to use a measure to return the status value thinking maybe that would fix the issue but it does not, the same sort problem exists...but maybe my measure is wrong.

 

Latest Resident Status = 
    CALCULATE(
        LASTNONBLANK('Status and Care History'[Resident Status], MAX('Status and Care History'[Date1])),
    FILTER('Calendar', 'Calendar'[Date] <= MAX('Calendar'[Date])))

 

Highlighted
Super User IV
Super User IV

Re: Sorting text in a matrix

@ctaylor , refer how I have done it , if this can help

https://www.dropbox.com/s/94zxohjgoaan28e/DescMonthSort.pbix?dl=0

 

I used month on column



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

Re: Sorting text in a matrix

@amitchandak 

You must not be understanding what I am trying to do and why it's not working.

 

The problem is when displaying in a vertical matrix (yours is horizontal), the text fields (you used and summed numbers) regarding the status and care level at the top level are being sorted a-z instead of by the date.  When a row is expanded, the values in each row are correct and sorted in the order I would expect.  But when you look at the top line (collapsed or expanded) the text values displayed have nothing to do with first or last date, but are first or last accoriding to alphabetical sort. Any attempt to try to set the sort by in the modeling tab ends up with this error.
image.png

 

I need the collapsed top line values to be the most current row of data by date. If a measure will do it, that would be great.  I posted a measure but it's clearly not working as intended.  

Highlighted
Super User IV
Super User IV

Re: Sorting text in a matrix

@ctaylor , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

Re: Sorting text in a matrix

@amitchandak 

The model that this is a part of is gigantic.  There's no feasible way to strip the data down to send a pbix file.

Also, please look back at my posts to see images of what is going on.

 

Here is a sample of data from that table.  
Date1 was your suggestion to add, but it's the same as From Date.  That is what is the active relationship out to Calendar[Date].  The only other relationship this table has is to a transition table that connects tenants up to a multitude of other tables.

 

Tenant HMYHistory HMYResident StatusFrom DateTo DateRes Care As OfCare LevelDate RankDate1
10744617213Current11/1/201411/30/201411/1/2014Independent Living Rent193011/1/2014
10744617214Current12/1/201412/30/201411/1/2014Independent Living Rent191012/1/2014
10744617215Moved Out12/31/20142/18/202011/1/2014Independent Living Rent188912/31/2014
10744617212Current8/1/201410/31/20148/1/2014Independent Living Rent19978/1/2014
10744617211Current3/1/20147/31/20143/1/2014 20723/1/2014
10744817219Moved Out11/30/2017 12/1/2016AL Care 288311/30/2017
10744817218On Leave10/26/201711/29/201712/1/2016AL Care 291810/26/2017
10744817217Current5/10/201310/25/20175/10/2013 21315/10/2013
10745017223Current4/1/20159/8/20154/1/2015Medicaid Room & Board18124/1/2015
10745017227Moved Out3/3/2016 4/1/2015Medicaid Room & Board14893/3/2016
10745017224On Leave9/9/20159/14/20154/1/2015Medicaid Room & Board16609/9/2015
10745017226On Leave1/23/20163/2/20164/1/2015Medicaid Room & Board15291/23/2016
10745017221Current8/3/20137/31/20148/3/2013 21128/3/2013
10745017222Current8/1/20143/31/20158/1/2014Independent Living Rent19978/1/2014
10745017225Current9/15/20151/22/20164/1/2015Medicaid Room & Board16549/15/2015
10745217233Current10/1/20162/9/201710/1/2016None129210/1/2016
10745217234On Leave2/10/20172/11/20171/1/2017Medicaid Room & Board11642/10/2017
10745217229Current11/2/20137/31/201411/2/2013 209011/2/2013
10745217230Current8/1/201412/16/20148/1/2014Independent Living Rent19978/1/2014
10745217231Current12/17/20143/24/201512/17/2014Independent Living Rent189712/17/2014
10745217232Current3/25/20159/30/20163/25/2015AL Care 218193/25/2015
10745217235Moved Out2/12/2017 1/1/2017Medicaid Room & Board11622/12/2017
10745417241Moved Out12/26/2014 8/1/2014Medicaid Room & Board189212/26/2014
10745417239On Leave11/13/201411/20/20148/1/2014Medicaid Room & Board192311/13/2014
10745417240On Leave11/21/201412/25/20148/1/2014Medicaid Room & Board191611/21/2014
10745417237Current5/11/20134/30/20145/11/2013 21305/11/2013
10745417238Current5/1/201411/12/20148/1/2013Meds Premium20495/1/2014
1074569678Current7/1/2017 7/1/2017None10297/1/2017
1074569676On Leave2/21/20173/7/20177/1/2016Independent Living Rent11532/21/2017
1074569674Current6/8/20136/1/20146/8/2013 21256/8/2013
1074569675Current6/2/20142/20/20176/2/2014 20346/2/2014
1074569677Current3/8/20176/30/20177/1/2016Independent Living Rent11383/8/2017
10745817245Moved Out1/31/2015 2/14/2014AL Care 118641/31/2015
10745817243Current3/5/200512/31/20143/5/2005 23203/5/2005
10745817244Current1/1/20151/30/20152/14/2014AL Care 118881/1/2015
10746017247Current5/5/20127/31/20145/5/2012 21875/5/2012
10746017248Current8/1/201410/25/20148/1/2014Independent Living Rent19978/1/2014
10746017249Moved Out10/26/2014 8/1/2014Independent Living Rent193510/26/2014
1074629682Current1/23/20154/29/20161/23/2015AL Care 218711/23/2015
1074629684Current5/1/201612/24/20167/1/2015AL Care 214365/1/2016
1074629686Current1/27/20176/4/201910/1/2016AL Care 211781/27/2017
1074629681Current8/1/20141/22/20158/1/2014Independent Living Rent19978/1/2014
10746215716Moved Out7/30/2019 4/1/2019AL Care 22937/30/2019
1074629683On Leave4/30/20164/30/20167/1/2015AL Care 214374/30/2016
1074629685On Leave12/25/20161/26/201710/1/2016AL Care 2121112/25/2016
10746215403On Leave6/5/20197/29/20194/1/2019AL Care 23486/5/2019
1074629680Current5/16/20097/31/20145/16/2009 22765/16/2009
Highlighted
Super User IV
Super User IV

Re: Sorting text in a matrix

 Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

Re: Sorting text in a matrix

@amitchandak 

I already said that this is a part of a model that is just way too large to strip down and obfuscate data.


Here's the data. 

Table Data 

 

I've already posted twice what the matrix looks like, and pointed out in red examples of the top level row is incorrect.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors