March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi Experts, Hope you're well!!
I need help with this problem I have which should hopefully be easy for you guys,
The Problem as follows...
ID | Delivery Day | New Delivery Day |
AAA | 5 | 5 |
AAA | 1 | 5 |
BBB | 2 | 2 |
BBB | 3 | 2 |
BBB | 4 | 2 |
CCC | 2 | 5 |
CCC | 4 | 5 |
CCC | 5 | 5 |
CCC | 5 | 5 |
DDD | 1 | 1 |
DDD | 1 | 1 |
DDD | 2 | 1 |
DDD | 3 | 1 |
I would like the logic where...
If the ID has a delivery day 5 at all then assign all of the delivery day ID as 5, but if there is no 5 then assign the delivery day as the lowest day displayed. I have provided it as a working example above.
Any help would be greatly appreciated!! 😊
Many Thanks,
Taylor
Solved! Go to Solution.
hi @Ttaylor9870
you may try to add a calculated column like:
Column =
VAR _list =
CALCULATETABLE(
VALUES(data[Delivery Day]),
data[ID] = EARLIER(data[ID]),
ALLEXCEPT(data, data[ID])
)
RETURN
IF(
5 IN _list,
5,
MINX(
FILTER(data, data[ID] = EARLIER(data[ID])),
data[Delivery Day]
)
)
it worked like:
Hi @Ttaylor9870
I created at start 4 measures: 2 of them Min and Max simple for Delivery day and another 2 for calculating row level for these Min /Max items.
Adjust Sheet5 to your table name
I hope this help
Proud to be a Super User!
hi @Ttaylor9870
you may try to add a calculated column like:
Column =
VAR _list =
CALCULATETABLE(
VALUES(data[Delivery Day]),
data[ID] = EARLIER(data[ID]),
ALLEXCEPT(data, data[ID])
)
RETURN
IF(
5 IN _list,
5,
MINX(
FILTER(data, data[ID] = EARLIER(data[ID])),
data[Delivery Day]
)
)
it worked like:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
27 | |
27 | |
21 | |
19 |