Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
@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
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])))
@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
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.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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.
I've already posted twice what the matrix looks like, and pointed out in red examples of the top level row is incorrect.
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.
@ctaylor , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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 |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |