Forum Discussion

EDANohra's avatar
EDANohra
Regular Visitor
2 years ago
Solved

How to add a logic to filter

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.

 

FacilityNameFacilityIDOrderDatePatientIDMealPeriodOrderMode
Sample HospitalSH01-May-24KH00000000BREAKFASTAuto Generated
Sample HospitalSH01-May-24KH00000000LUNCHAuto Generated
Sample HospitalSH01-May-24KH00000000SUPPERAuto Generated
Sample HospitalSH02-May-24KH00000000BREAKFASTAuto Generated

 

I would appreciate any help!

Regards,

Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

    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"

     

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    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
    )
    )

  • EDANohra's avatar
    EDANohra
    Regular Visitor
    2 years ago

    Hello Bhanu,

    It unfortunately didn't work out for my dataset. 

    Appreciate your effort. 

  • jgeddes's avatar
    jgeddes
    Super User
    2 years ago

    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"