topic DAX Help to assign a value based on another value in DAX Commands and Tips

DAX Help to assign a value based on another value

Ttaylor9870 — Mon, 29 May 2023 09:13:16 GMT

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

Re: DAX Help to assign a value based on another value

FreemanZ — Mon, 29 May 2023 09:51:53 GMT

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:

Re: DAX Help to assign a value based on another value

some_bih — Mon, 29 May 2023 09:56:12 GMT

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])

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)

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

Row Level Delivery Day Max =

CALCULATE(

[M_Max_Del_Day],

ALLEXCEPT(Sheet5,Sheet5[ID])

)

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]