Need help with DAX comparision with all the records

[ankitkalsara]

Hi team,

I am having issues while creating new column "Shipping Status" with below logic.

• if the task id of the current task is less than the task id of Shipping task --> it should be marked as"Pre-Shipping"
• if the task id of the current task is greater than the task id of Shipping task --> it should be marked as"Post-Shipping"
• if the task name is Shipping --> it should be marked as "Shipping"

Relationship --> A given project contains multiple tasks. Any help will be much appreciated. 

Project	Task Id	Task Name	Shipping Status
1	1	Task 11	Pre-Shipping
1	2	Task 12	Pre-Shipping
1	3	Shipping	Shipping
1	4	Task 14	Post-Shipping
1	5	Task 15	Post-Shipping
2	1	Task 21	Pre-Shipping
2	2	Task 22	Pre-Shipping
2	3	Shipping	Shipping
2	4	Task 23	Post-Shipping

[Jihwan_Kim · Accepted solution]

Hi, @ankitkalsara 

Please try the below for creating a new column.

Shipping Status Column =
VAR currentproject = 'Table'[Project ]
VAR shippingtaskid =
CALCULATE (
SELECTEDVALUE ( 'Table'[Task Id] ),
FILTER (
ALL ( 'Table' ),
'Table'[Project ] = currentproject
&& 'Table'[Task Name] = "Shipping"
)
)
RETURN
SWITCH (
TRUE (),
'Table'[Task Id] < shippingtaskid
&& 'Table'[Project ] = currentproject, "Pre-Shipping",
'Table'[Task Id] > shippingtaskid
&& 'Table'[Project ] = currentproject, "Post-Shipping",
"Shipping"
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[ankitkalsara]

Hi @Jihwan_Kim ,

Thank you very much for the suggestion, it works exactly the way I wanted 🙂