Create calculated column based on values in other columns

[OPS-MLTSD]

Client ID	In Placement	Placement Hours	Placement Reason
12345	Yes	39	after event
12345	Yes	39	3 month followup
12345	Yes	39	6 month followup
43211	No
43211	No		3 month followup
43211	Yes	50	6 month followup
22222
22222
22222

Hello, I have a sample table above, I am trying create a calculated column for clients who are working at a placement (In Placement) for more than 30 hours (PLacement Hours). But specificially for clients who attained the placement after attending the event(placement reason = after event)

So this data is collected at 3 different time points:

1. Right after client attends at event

2. 3 months after they have attended an event

3. 6 months after they have attended an event

The reason data is collected at different time points is because we want to know whether client has a placement or not because of the service we provide to them. Clients are called after 3 months of atending the event and asked again if they have a placement, and then again at 6 months to see if they have a placement and if so, are they still in the same place.

I created a dax that looks like this:

Working 30+ hours at Placement after attending event= SWITCH(
TRUE(),
'Table'[In Placement]="Yes" && 'Table'[Placement Hours]>=30 && 'Table'[Placement Reason]="after event", "Yes", "No")

However, the problem is, if [Placement Reason] is specifically "after event" it's setting the other 2 records for the case (3 month followup, 6 month followup) to 'No' and that's messing up the filter.

Out of the clients who achieved a placement right after attending an event, how can I create a calculated column to show only clients who are working 30hours or more so I want the table to look like this:

Client ID	In Placement	Placement Hours	Placement Reason	working 30+ hours at placement after attending event
12345	Yes	39	after event	Yes
12345	Yes	39	3 month followup	follow-up
12345	Yes	39	6 month followup	follow-up
43211	No			No
43211	No		3 month followup	follow-up
43211	Yes	50	6 month followup	follow-up
22222				No
22222				unknown
22222				unknown

[Anonymous · Accepted solution]

Hi @OPS-MLTSD 

You need a sort to distinguish the time of Placement Reason. Add an Index column by Group in Power Query. 

Firstly group all rows by Client ID column. Then add a custom column.

Index
=
Table.AddIndexColumn([Rows],"Index",1)

Then expand and transform your table. New table looks like as below.

Create a new calculated column.

Working 30+ hours at Placement after attending event = 
IF (
    'Table'[Index] = 1,
    SWITCH (
        TRUE (),
        'Table'[In Placement] = "Yes"
            && 'Table'[Placement Hours] >= 30
            && 'Table'[Placement Reason] = "after event", "Yes",
        "No"
    ),
    IF (
        CONTAINSSTRING ( 'Table'[Placement Reason], "followup" ),
        "follow-up",
        "unknown"
    )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OPS-MLTSD]

thank you!

[lbendlin]

Power BI does not know how to sort. Your source data needs to contain a sortable column like a time stamp.

How is your desired outcome different from your source data? Where does the "unknown" value come from for 22222?