Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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,
Solved! Go to 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"
Proud to be a Super User! | |
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"
Proud to be a Super User! | |
Hello Bhanu,
It unfortunately didn't work out for my dataset.
Appreciate your effort.
@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
)
)
Proud to be a Super User! |
|