Sorting text in a matrix

[ctaylor]

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.

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:

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!

[amitchandak]

@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

[ctaylor]

@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])))

[amitchandak]

@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

[ctaylor]

@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.

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.  

[amitchandak]

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

[ctaylor]

@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.

[ctaylor]

I was hoping a MSFT community person would have popped in, whenever you guys show up you always present a nice and easy solution.

Here is the solution I came up with to fix the issue for anyone wondering.

My data already contained an index in the [History HMY] column. So I first made a measure to find the max value in the time frame.

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

Then a LOOKUP measure to find the value based on the row corresponding to the previous measure.

Latest Resident Status = 
    LOOKUPVALUE('Status and Care History'[Resident Status], 'Status and Care History'[History HMY], [Latest Resident Status HMY])

Same concept for Care Status

Now, when I put both of the measures in a matrix I get the most recent value to show in the top level values as well as when collapsed.

[amitchandak]

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

[ctaylor]

@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 HMY	History HMY	Resident Status	From Date	To Date	Res Care As Of	Care Level	Date Rank	Date1
107446	17213	Current	11/1/2014	11/30/2014	11/1/2014	Independent Living Rent	1930	11/1/2014
107446	17214	Current	12/1/2014	12/30/2014	11/1/2014	Independent Living Rent	1910	12/1/2014
107446	17215	Moved Out	12/31/2014	2/18/2020	11/1/2014	Independent Living Rent	1889	12/31/2014
107446	17212	Current	8/1/2014	10/31/2014	8/1/2014	Independent Living Rent	1997	8/1/2014
107446	17211	Current	3/1/2014	7/31/2014	3/1/2014		2072	3/1/2014
107448	17219	Moved Out	11/30/2017		12/1/2016	AL Care 2	883	11/30/2017
107448	17218	On Leave	10/26/2017	11/29/2017	12/1/2016	AL Care 2	918	10/26/2017
107448	17217	Current	5/10/2013	10/25/2017	5/10/2013		2131	5/10/2013
107450	17223	Current	4/1/2015	9/8/2015	4/1/2015	Medicaid Room & Board	1812	4/1/2015
107450	17227	Moved Out	3/3/2016		4/1/2015	Medicaid Room & Board	1489	3/3/2016
107450	17224	On Leave	9/9/2015	9/14/2015	4/1/2015	Medicaid Room & Board	1660	9/9/2015
107450	17226	On Leave	1/23/2016	3/2/2016	4/1/2015	Medicaid Room & Board	1529	1/23/2016
107450	17221	Current	8/3/2013	7/31/2014	8/3/2013		2112	8/3/2013
107450	17222	Current	8/1/2014	3/31/2015	8/1/2014	Independent Living Rent	1997	8/1/2014
107450	17225	Current	9/15/2015	1/22/2016	4/1/2015	Medicaid Room & Board	1654	9/15/2015
107452	17233	Current	10/1/2016	2/9/2017	10/1/2016	None	1292	10/1/2016
107452	17234	On Leave	2/10/2017	2/11/2017	1/1/2017	Medicaid Room & Board	1164	2/10/2017
107452	17229	Current	11/2/2013	7/31/2014	11/2/2013		2090	11/2/2013
107452	17230	Current	8/1/2014	12/16/2014	8/1/2014	Independent Living Rent	1997	8/1/2014
107452	17231	Current	12/17/2014	3/24/2015	12/17/2014	Independent Living Rent	1897	12/17/2014
107452	17232	Current	3/25/2015	9/30/2016	3/25/2015	AL Care 2	1819	3/25/2015
107452	17235	Moved Out	2/12/2017		1/1/2017	Medicaid Room & Board	1162	2/12/2017
107454	17241	Moved Out	12/26/2014		8/1/2014	Medicaid Room & Board	1892	12/26/2014
107454	17239	On Leave	11/13/2014	11/20/2014	8/1/2014	Medicaid Room & Board	1923	11/13/2014
107454	17240	On Leave	11/21/2014	12/25/2014	8/1/2014	Medicaid Room & Board	1916	11/21/2014
107454	17237	Current	5/11/2013	4/30/2014	5/11/2013		2130	5/11/2013
107454	17238	Current	5/1/2014	11/12/2014	8/1/2013	Meds Premium	2049	5/1/2014
107456	9678	Current	7/1/2017		7/1/2017	None	1029	7/1/2017
107456	9676	On Leave	2/21/2017	3/7/2017	7/1/2016	Independent Living Rent	1153	2/21/2017
107456	9674	Current	6/8/2013	6/1/2014	6/8/2013		2125	6/8/2013
107456	9675	Current	6/2/2014	2/20/2017	6/2/2014		2034	6/2/2014
107456	9677	Current	3/8/2017	6/30/2017	7/1/2016	Independent Living Rent	1138	3/8/2017
107458	17245	Moved Out	1/31/2015		2/14/2014	AL Care 1	1864	1/31/2015
107458	17243	Current	3/5/2005	12/31/2014	3/5/2005		2320	3/5/2005
107458	17244	Current	1/1/2015	1/30/2015	2/14/2014	AL Care 1	1888	1/1/2015
107460	17247	Current	5/5/2012	7/31/2014	5/5/2012		2187	5/5/2012
107460	17248	Current	8/1/2014	10/25/2014	8/1/2014	Independent Living Rent	1997	8/1/2014
107460	17249	Moved Out	10/26/2014		8/1/2014	Independent Living Rent	1935	10/26/2014
107462	9682	Current	1/23/2015	4/29/2016	1/23/2015	AL Care 2	1871	1/23/2015
107462	9684	Current	5/1/2016	12/24/2016	7/1/2015	AL Care 2	1436	5/1/2016
107462	9686	Current	1/27/2017	6/4/2019	10/1/2016	AL Care 2	1178	1/27/2017
107462	9681	Current	8/1/2014	1/22/2015	8/1/2014	Independent Living Rent	1997	8/1/2014
107462	15716	Moved Out	7/30/2019		4/1/2019	AL Care 2	293	7/30/2019
107462	9683	On Leave	4/30/2016	4/30/2016	7/1/2015	AL Care 2	1437	4/30/2016
107462	9685	On Leave	12/25/2016	1/26/2017	10/1/2016	AL Care 2	1211	12/25/2016
107462	15403	On Leave	6/5/2019	7/29/2019	4/1/2019	AL Care 2	348	6/5/2019
107462	9680	Current	5/16/2009	7/31/2014	5/16/2009		2276	5/16/2009

[ctaylor]

@amitchandak 

Let me see if I understand you correctly.

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.

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