How to add a logic to filter

[EDANohra]

Dear Microsoft Community,

I'm quite new to the PBI, and facing one dataset that I need to find a way to solve it.

What do I want to achieve?

Our service standards say that if a patient is admitted to a hospital, the first three meals can be automatically generated without any personalized order-taking process. In the dataset that I have, there are no details where I can exclude these three first meals of a patient from my calculation of "auto-generated orders"

I have found the admission date of a patient from the first date of order and can exclude the first-day meals from my calculation, but this will be inaccurate because if the patient was admitted in the afternoon, he/she would be only served one meal that will be called supper. Therefore, I want to put a logic saying that, from the first meal order of a patient exclude three consecutive served meals.

My data set is illustrated below for your understanding.

In the below sample, I should not have 4 autogenerated orders in my report but only one that was served on 2nd May.

FacilityName	FacilityID	OrderDate	PatientID	MealPeriod	OrderMode
Sample Hospital	SH	01-May-24	KH00000000	BREAKFAST	Auto Generated
Sample Hospital	SH	01-May-24	KH00000000	LUNCH	Auto Generated
Sample Hospital	SH	01-May-24	KH00000000	SUPPER	Auto Generated
Sample Hospital	SH	02-May-24	KH00000000	BREAKFAST	Auto Generated

I would appreciate any help!

Regards,

[jgeddes · Accepted solution]

If you are looking for a result of...

The following code example has the steps using your example data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MLchJVfDILy7ILEnMUdJRCvYAEgaGur6JlbpGJkC2t4cBFAA5TkGujt5ujsEhQLZjaUm+gntqXmpRYklqilKsDunG+YT6OXtQx6jg0IAA1yDSzDIiw5exAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FacilityName = _t, FacilityID = _t, OrderDate = _t, PatientID = _t, MealPeriod = _t, OrderMode = _t]),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {
            {"FacilityName", type text}, 
            {"FacilityID", type text}, 
            {"OrderDate", type date}, 
            {"PatientID", type text}, 
            {"MealPeriod", type text}, 
            {"OrderMode", type text}
        }
    ),
    Custom1 = 
    Table.Buffer(
        Table.Sort(
            #"Changed Type", 
            {"OrderDate", Order.Ascending}
        )
    ),
    #"Grouped Rows" = 
    Table.Group(
        Custom1, 
        {"FacilityName", "FacilityID", "PatientID"}, 
        {
            {
                "innerTable", 
                each Table.SelectColumns(_, {"OrderDate", "MealPeriod"}), 
                type table [FacilityName=nullable text, FacilityID=nullable text, OrderDate=nullable date, PatientID=nullable text, MealPeriod=nullable text, OrderMode=nullable text]
            }
        }
    ),
    Custom2 = 
    Table.TransformColumns(
        #"Grouped Rows", 
        {
            {
                "innerTable", 
                each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)
            }
        }
    ),
    Custom3 = 
    Table.TransformColumns(
        Custom2, 
        {
            {
                "innerTable", 
                each Table.AddColumn(_, "Order Mode", each if [Index] > 3 then "Auto Generated" else "Excluded"), 
                type table [OrderDate=date, MealPeriod=text, Order Mode=text]
            }
        }
    ),
    #"Expanded innerTable" = 
    Table.ExpandTableColumn(
        Custom3, 
        "innerTable", 
        {"OrderDate", "MealPeriod", "Order Mode"}
    )
in
    #"Expanded innerTable"

[EDANohra]

Hello Bhanu,

It unfortunately didn't work out for my dataset. 

Appreciate your effort. 

[bhanu_gautam]

@EDANohra , To achieve this

Sort the Data: Ensure your data is sorted by PatientID and OrderDate in ascending order.

Create a Calculated Column for Sequential Meal Number

MealNumber =
VAR CurrentPatient = 'Table'[PatientID]
VAR CurrentDate = 'Table'[OrderDate]
RETURN
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[PatientID] = CurrentPatient &&
'Table'[OrderDate] <= CurrentDate
)
)

Then create a measure to filter out top 3

FilteredAutoGeneratedOrders =
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[OrderMode] = "Auto Generated" &&
'Table'[MealNumber] > 3
)
)