Forum Discussion

Ttaylor9870's avatar
Ttaylor9870
Icon for Helper III rankHelper III
3 years ago
Solved

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...

 

IDDelivery DayNew Delivery Day
AAA55
AAA15
BBB22
BBB32
BBB42
CCC25
CCC45
CCC55
CCC55
DDD11
DDD11
DDD21
DDD31


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

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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:

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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:

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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]