Forum Discussion
Last known status
Hi RYK16
1) OK, to do it right you have to be able to track a wound throughout all its lifetime, so to speak. Where then is a field in your fact table that enables that? Where's something like WoundID? Can't see it...
2) Second thing. Why would you leave blanks in the fact table in the PressureUlcerStage column? It makes calculation much harder than the should be. Please use Power Query to get rid of all the ugly blanks (you should hardly ever have blanks in any columns in a well-designed dimensional model) and replace them with the latest available status for the wound. In PQ it's easy.
If you tell me how to track a wound, then I'll tell you how to correctly write the measure. But please remember about point 2) as well.
Hi Daxer,
Thank you for your response. Yes you are correct it does track the wound throughout its lifetime, when I view WoundObsDate, this is every single observation for the Wound. Yes each Wound has a Wound ID associated. I did have this in my table but I was counting it.
You are right, it is strange to have blank values. This data is entered in a wound chart in our resident management system. It is not a mandotory field and I suppose comes down to users making sure they classify the wound at each observation. I think this isn't always done because classification hasn't changed or possibly because it seems to mainly be entered when the Clinical Nurse does their review of the wound. So unfortunatly due to the way the system is and relying on the information to be put in, there will always be blanks. In your point 2, you said I can remove the blanks by replacing them with the lastest available status in Power Query. How would I do that?
I have managed to have my Matrix display the latest Stage value, per the measure suggested by ERD below, but am curious how you would do it. Would love to learn different ways, as obvioulsy some may suit certain scenarios better. I also added additional outcomes I would like to achieve in my reply to ERD below if you are able to assist.
Thank you so much for your response!
The fact that users do something is completely irrelevant to what your model should look like. Really. Power Query is a fabulous tool that enables you to massage your data into the shape YOU want, not what your users (with their shabby habits) force you into. And your model should be as simple and as clear as possible. If you take data the way it is as it comes, you'll almost always face problems - either structural or performance-related; and if not now, then surely later on. A good model is one where DAX is very simple and therefore blazingly fast even on thousands of milions of rows. Yes, you heard it 🙂
Once I come back, I'll try to show you both - the Power Query query and the DAX. Stay tuned.