Forum Discussion

RYK16's avatar
RYK16
Frequent Visitor
5 years ago

Last known status

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 cha...
ERD's avatar
ERD
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
RYK16
Frequent Visitor
5 years ago

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 🙂