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.
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:
User | Count |
---|---|
16 | |
14 | |
13 | |
12 | |
11 |
User | Count |
---|---|
19 | |
15 | |
15 | |
11 | |
10 |