Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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! |
|
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |