Last known status

[RYK16]

Hi everyone,

I am still learning more about DAX and measures and am needing some help. I have the below data, for residents Wounds and the stages they go through at each observation. They can change ‘Stage” at each observation/check or even be left blank usually where no change. I can view each observation and the stage in table form no worries (below).

I am stating in a table just to see if I can get the info I need before I build all the visualisations I want. What I want to be able to view is ‘ number of wounds for each stage (Stage 1, Stage 2, Stage 3 etc) for entire facility by month. However, when I filter by the Stages it will display the resident/wound for each stage it has been in, not just the last stage. Using the example above Resident 1295 will show as Stage 1 and Stage 2 and blank.

What I want to be able to do is view ONLY the last recorded Stage for the Wounds ie Current stage. In some cases the last Stage observation is left blank so I then need the last known stage to display.

I changed the Wound Observation date to ‘Latest’ but this will still show me every wound stage and the latest date of each stage the wound has moved through.

I played with a couple measures I found online which I don't think ere exacly right for me. I did Last non blank but it doesn’t return the last recorded Stage by ‘latest date’ it displays Last Stage alphabetically I think. Below it shows stage 2 but if you go back to the initial list of observations you can see the last stage should be stage 1.

Any help would be appreciated.

Thank you

[Anonymous]

// With the table where there are no blank stages...
// To be able to view the how many wounds
// there are with some latest stage, you
// have to have a disconnected dimension 
// table called, say, 'Latest Stages' with
// a column [Latest Stage]. You can slice
// the data by many attributes and the measure
// below should return the right result. In particular,
// you can slice by Residents, Facilities, Wound Type...
// There are also attributes slicing by which makes
// no sense. For instance, by PressureUlcerStage.
// In general, you should NEVER slice by attributes
// that are held by the fact table directly.
// Only by dimensions. There is a whole article on
// www.sqlbi.com which explains in great detail
// why slicing by attributes held by fact tables
// is a very bad idea. Such slicing can even lead
// to wrong figures that will never be caught...
// So, consider yourself warned :)

[# Wounds with Latest Status] =
// Get the last date in the context;
// I assume that Dates is connected to
// the fact table on the WoundObsDate
// column.
var vLatestDate = MAX( Dates[Date] )
// Get the currently visible stage;
// If there is more than 1 stage visible
// this will return blank.
var vStage = SELECTEDVALUE( 'Latest Stages'[Latest Stage] )
var vWoundsWithLatestDates =
    ADDCOLUMNS(
        // Get all WoundID's in the current
        // context.
        DISTINCT( T[WoundID] ),
        // For each calculate the last entry
        // in the fact table before or on
        // the latest date in the context.
        "@LatestDate",
            CALCULATE(
                MAX( T[WoundObsDate] ),
                T[WoundObsDate] <= vLatestDate,
                // All( Dates ) removes all
                // existing filters from Dates.
                ALL( Dates )
            )
    )
var vResult =
    CALCULATE(
        // DISTINCTCOUNT could be replaced
        // by COUNTROWS( T ) since I assume
        // each wound has no more than 1 entry
        // on each particular day. If this is
        // not the case, you have to have a field
        // that will order the wound's events
        // in a temporal order (but I can't see
        // this in your table, hence the assumption).
        DISTINCTCOUNT( T[WoundID] ),
        TREATAS(
            vWoundsWithLatestDates,
            T[WoundID],
            T[WoundObsDate]
        ),
        // From the wounds selected by the filter
        // above, select only those that are in
        // vStage.
        T[PressureUlcerStage] = vStage,
        ALL( Dates )
    )
RETURN 
    vResult

    
// To be able to see wounds with their
// latest stages (latest records) you
// just have to write a filtering measure
// that will leave only the latest
// records in the visual. So, if the record
// is the latest in the current context
// return 1, otherwise 0, and use this measure
// in the Filter Pane of the visual. But to be
// able to action this treat, the Dates table
// should be DISCONNECTED from your fact table.
// Otherwise, selecting anything from Dates will
// immediately filter your fact table which defeats
// the purpose.

[Anonymous]

Hi @RYK16 

Here's the M script that does remove the blank/empty statuses/stages and replaces them with the last known status. Just pop this in to your Advanced Editor in Power Query and see it working step by step. You should do the same thing to your data to tidy it up.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY5LDoAgDESv0rB2QQs3cUlYVKMkJqIhen+JH5DAsvPyZmqMQNEJhIU9kCQZj/5gNwEK2z2QZEXpD+dp+GCKFcLKoWzUN6X2HCXYmAOVaFmcG6N0+iJWT1y9gYnGJd5DLWng07V+f6VtPLJkLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WoundID = _t, Date = _t, Stage = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WoundID", Int64.Type}, {"Date", type date}, {"Stage", type text}}),
    // You have to sort the rows first by WoundID, then by Date
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"WoundID", Order.Ascending}, {"Date", Order.Ascending}}),
    // I had to replace the empty strings with reall nulls, so that the Fill Down step works OK
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","",null,Replacer.ReplaceValue,{"Stage"}),
    // Replacing the null/empty statuses with the latest known status
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Stage"})
in
    #"Filled Down"

I'll post the accompanying DAX soon.

[Anonymous]

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.

[RYK16]

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!

[Anonymous]

@RYK16 

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.

[ERD]

Hi @RYK16 ,

It's a bit unclear what you want to achieve.
Latest Stage from your examples:

#LatestStage = 
VAR tableWithLatestDate =
    CALCULATETABLE(
    	ADDCOLUMNS(
    		SUMMARIZE(T, T[ResId],T[W_Loc]),
    		"MaxDate", CALCULATE(MAX(T[W_Obs_date]))
    		),
    	KEEPFILTERS(T[Stage] <> BLANK ()))
RETURN
CALCULATE(
    MAX(T[Stage]),
    TREATAS(tableWithLatestDate, T[ResId],T[W_Loc],T[W_Obs_date])
)

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[RYK16]

Hi ERD,

I apologise, my mind was in 2 thoughts when I was positng. I suppose I have a couple outcomes I am after.

Scenario 1 was to be able to view all residents wounds in table form and display the latest wound stage entered for that wound (some cases this was blank). Your measure resolved that nicely thank you.

I removed the Wound observation dates and can now see only 1 stage against each wound which is the last stage noted. Perfect!

Scenario #2

I would like to be able to display a count of current wounds by latest wound stage. Previously if I were to try capture this it would display the same wound multiple times if it has been in more than 1 stage, eg display in Stage 1 and Stage 2 count. I would like it to be something like:

Scenario #3
I would like to be able to display count of wounds by stage per month. This will be all wounds (active, closed) and all residents (active, discharged). This would be the last stage of each wound (Wound ID) within that month. The one wound (Wound ID) may appear across multiple months as the wound was still active but I only need the last stage of the wound for that month ( may have downgraded from Stage 2 to Stage 1 within a month). And keeping in mind some observations for the stage is left blank, but I need to last recorded Stage value. It may look something like this

I hope this was a little clearer. Thank you so much 🙂