cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ankitkalsara
Frequent Visitor

Need help with DAX comparision with all the records

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 IdTask NameShipping Status
11Task 11Pre-Shipping
12Task 12Pre-Shipping
13ShippingShipping
14Task 14Post-Shipping
15Task 15Post-Shipping
21Task 21Pre-Shipping
22Task 22Pre-Shipping
23ShippingShipping
24Task 23Post-Shipping
1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

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/

 

Picture1.png

View solution in original post

2 REPLIES 2
Jihwan_Kim
Community Champion
Community Champion

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/

 

Picture1.png

View solution in original post

Hi @Jihwan_Kim ,

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors