cancel
Showing results for
Search instead for
Did you mean:
Helper III

## DAX Help to assign a value based on another value

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

1 ACCEPTED SOLUTION
Community Champion

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:

2 REPLIES 2
Memorable Member

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

1. M_Simple_max = MAX(Sheet5[Delivery Day])
2. M_Simple_min = MIN(Sheet5[Delivery Day])
3.
M_Max_Del_Day =
VAR _max_value=[M_Simple_max]
VAR _summarize_table=
ADDCOLUMNS(
SUMMARIZE(Sheet5,Sheet5[ID])
, "@max",[M_Simple_max]
)

VAR _filtered_table=
FILTER(_summarize_table,[@max]=_max_value)
RETURN
CALCULATE(max(Sheet5[Delivery Day]),_summarize_table)
4.
M_MIN_Del_Day =
VAR _min_value=[M_Simple_min]
VAR _summarize_table=
ADDCOLUMNS(
SUMMARIZE(Sheet5,Sheet5[ID])
, "@min",[M_Simple_min]
)

VAR _filtered_table=
FILTER(_summarize_table,[@min]=_min_value)
RETURN
CALCULATE(min(Sheet5[Delivery Day]),_summarize_table)

Next, I created 2 columns to calcualte on Row level Max / Min
5.
Row Level Delivery Day Max =
CALCULATE(
[M_Max_Del_Day],
ALLEXCEPT(Sheet5,Sheet5[ID])
)
6.
Row Level Delivery Day Min =
CALCULATE(
[M_MIN_Del_Day],
ALLEXCEPT(Sheet5,Sheet5[ID])
)
7. I finaly add IF statment in column
Test New Delivery Day =
IF(Sheet5[Row Level Delivery Day Max]=5,
Sheet5[Row Level Delivery Day Max],
Sheet5[Row Level Delivery Day Min]
)

At end I checked with your data, column
Check Test vs New = Sheet5[Test New Delivery Day]-Sheet5[Test New Delivery Day]
Sarajevo, Bosnia and Herzegovina
Community Champion

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:

## Helpful resources

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors