If and query: if ID is in one table and based on a date before, and other criteria

[Anonymous]

Hi everyone

this is my first app i have done from scratch - please bear with me.

I have two datasets in it, one current and one historic. these are waitlist tables of patients.

i want to add a flag to the current table if a patient (ID field) was not in the previous snapshot from the historic table and didnt start their pathway on the current so i would call this a 'pop in', ie. they have popped onto the waitlist from nowhere.

i have been searching around this site and think im nearly there but its still not right:

popin = if(max(Historic[SnapShotDate]) <today() 

&& (not( PTL[RTTPathwayID] ) IN DISTINCT  (Historic[RTTPathwayID]) )

&& PTL[RTTStartDate] < max(Historic[SnapShotDate])

, "y" ,"n")

logically i thought this would work - any help appreciated 😉

thanks Liz

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Hi thank you; hope I done this right, inserting table:

first table is my current PTL.  i want to be able to flag based on RTTPathwayID whether or not they were on the previous 2 snapshots. so i have made a 2nd table showing 1 example that was not on last week but was on the previous week -so this is not a true popin. 

i also dont want to include them as a pop in if their RTT start date is in the last 2 weeks.

So a 'Popin' is any pathwayID that is not on previous 2 snapshots or has an RTT startdate more than 2/3 weeks ago..

thanks

RTTPathwayID	RTTStartDate	RTTWaitDays	Weeks	RTTSpecialtyCode	RTTSpecialty	LastUpdate	in
RJR3015592	15/12/2020	649	93	101	UROLOGY	26/09/2022	#N/A
RJR2954216	24/02/2021	578	83	110	TRAUMA & ORTHOPAEDICS	26/09/2022	#N/A
RJR3045061	16/03/2021	558	80	120	ENT	26/09/2022	#N/A
RJR3074006	08/04/2021	535	77	101	UROLOGY	26/09/2022	#N/A
RJR3059386	10/04/2021	533	77	101	UROLOGY	26/09/2022	#N/A
RJR3059604	13/04/2021	530	76	101	UROLOGY	26/09/2022	#N/A
RJR3063402	13/04/2021	530	76	101	UROLOGY	26/09/2022	#N/A

RTTPathwayID	RTTStartDate	WeeksWaited	RTTWaitDays	SpecialtyCode	Specialty	Division	RTTSpecialtyCode	RTTSpecialty	SnapShotDate
RJR3074006	08/04/2021	77	535	101	UROLOGY	Planned Care	101	UROLOGY	26/09/2022
RJR3074006	08/04/2021	75	521	101	UROLOGY	Planned Care	101	UROLOGY	12/09/2022
RJR3074006	08/04/2021	74	514	101	UROLOGY	Planned Care	101	UROLOGY	05/09/2022
RJR3074006	08/04/2021	73	507	101	UROLOGY	Planned Care	101	UROLOGY	29/08/2022

[lbendlin · Accepted solution]

i also dont want to include them as a pop in if their RTT start date is in the last 2 weeks

Please define what you mean by "last two weeks".

Apart from that here is the basic approach:

Table Snapshots:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvIKMjYwNzEwMFPSUTKw0Dcw0TcyMDIEcszNgYSpsSmQNDQACYQG+fv4u0cCWQE5iXl5qSkKzolFqVikjcz0DSxBxhgpxergtQJkuCmYSaIVhkbEWmECssLQhHQrDEyJtcIYZIWBORkBZakPNAliRSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RTTPathwayID = _t, RTTStartDate = _t, WeeksWaited = _t, RTTWaitDays = _t, SpecialtyCode = _t, Specialty = _t, Division = _t, RTTSpecialtyCode = _t, RTTSpecialty = _t, SnapShotDate = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"SnapShotDate", type date}}, "en-GB")
in
    #"Changed Type with Locale"

Table Current:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pc9NCgIxDAXgq8i4FfqaNOl0Oaj4gzpSx4Wox/D+Nl0IsxlQN4+25H2k93uT95nhRRI1i8aL8+QIhHLRkEomtnf4ktfcH/rNrZxIHZLNWWl+cl3zXFSKkgTyaiPBoVLWlNiWbCvlzR5ydz12s8cLIJ31edj252692i0vUzojCNRAX0b4o0vVzfV18/VpmGZiAGxJtA7hw7CUjPGb/zIkcau1MqL4J0oRrMIjyn4U9TtKOYD+o55v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RTTPathwayID = _t, RTTStartDate = _t, RTTWaitDays = _t, Weeks = _t, RTTSpecialtyCode = _t, RTTSpecialty = _t, LastUpdate = _t, #"in" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"RTTStartDate", type date},{"LastUpdate", type date}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", (k)=> Table.FirstN(Table.SelectRows(Snapshots,each [RTTPathwayID]=k[RTTPathwayID] and [SnapShotDate]<k[LastUpdate]),2))
in
    #"Added Custom"

Brief explanation: 

#"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", (k)=> Table.FirstN(Table.SelectRows(Snapshots,each [RTTPathwayID]=k[RTTPathwayID] and [SnapShotDate]<k[LastUpdate]),2))

Map the Snapshots table to the Current Table based on [RTTPathwayID]. Filter for dates that are older than the latest snapshot.  Grab the top two rows.