Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Ttaylor9870
Helper III
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...

 

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

1 ACCEPTED SOLUTION
FreemanZ
Super User
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:

FreemanZ_0-1685353906167.png

View solution in original post

2 REPLIES 2
some_bih
Super User
Super User

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

some_bih_0-1685354162230.png

 

 

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]




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






FreemanZ
Super User
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:

FreemanZ_0-1685353906167.png

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors