find a date value based on another column with conditions

[EF]

Hi all,

I have a fact table with each row another record. There is a date column and an event type column.

I am trying to measure the episode of care per client.  (Episode of Care trajectory is: Call-in; Intake; Admission; Discharge) 

However many clients have multiple episodes of care. 

If I just do Calculate(Min(FactTable[date]),(FactTable[EventType]="Call-in"), and then I put it in a matrix with clientId as rows, then it pulls the earliest call-in of the client, but many clients have multiple episodes of care. 

Same if I pull the MIN Intake date, it will just pull the earliest one.

Is there a way to mark the events as episode of cares?

Of course, to complicate matters, the data is messy and clients do not necessarily follow the trajectory perfectly (they can be missing a discharge, and then they can have a new episode of care, or they can have a Call-in and Intake, and then deny services, and then have a new Intake a month later which starts their trajectory)

Any ideas?

[az38]

hi @EF 

dont you have an episode ID in your data source? how is your data look like?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[EF]

Good question. Not all the records have an appointment ID (because they were pulled in and appended from various sources)

Maybe in Query Editor I can assign Epsiode IDs? Would that help? 

I guess I would go according to date. 

[az38]

@EF 

if you will have an Episode ID you will able to create a measure like

FirstDateByEpisode = CALCULATE(MIN(FactTable[date]),ALLEXCEPT(FactTable, FactTable[Episode ID])) 

which you could display in visuals

do not hesitate to give a kudo to useful posts and mark solutions as solution

[EF]

Hi @az38 

I added an episode ID and then created that measure.

I am not clear how it helps me though.

For example, if I want to calculate the length of time between call-in and intake per client, making sure that I am capturing the correct intake for the episode of care, how would I do it using the new measure?

[az38]

So, if I understand you correct you will need to create a measure like
FirstDateByEpisode = CALCULATE(MIN(FactTable[date]),ALLEXCEPT(FactTable, FactTable[Episode ID]), [event type]=“intake”) -
CALCULATE(MIN(FactTable[date]),ALLEXCEPT(FactTable, FactTable[Episode ID]), [event type]=“Call-In”)

[EF]

Thanks @az38 

Just got back to this after the weekend.

I tried your measure, when I put it in a table visualization it is giving me the same date for every client.

[az38]

@EF 

you haven't mentioned about this condition

so, either try

FirstDateByEpisode = CALCULATE(MIN(FactTable[date]),ALLEXCEPT(FactTable, FactTable[Episode ID],FactTable[Client]), [event type]=“intake”) -
CALCULATE(MIN(FactTable[date]),ALLEXCEPT(FactTable, FactTable[Episode ID],FactTable[Client]), [event type]=“Call-In”)

or share your data example 

do not hesitate to give a kudo to useful posts and mark solutions as solution