Forum Discussion

OPS-MLTSD's avatar
OPS-MLTSD
Icon for Post Patron rankPost Patron
4 years ago
Solved

Create calculated column based on values in other columns

Client IDIn PlacementPlacement HoursPlacement Reason
12345Yes39after event
12345Yes393 month followup
12345Yes396 month followup
43211No  
43211No  3 month followup
43211Yes506 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 IDIn PlacementPlacement HoursPlacement Reasonworking 30+ hours at placement after attending event
12345Yes39after eventYes
12345Yes393 month followupfollow-up
12345Yes396 month followupfollow-up
43211No  No
43211No  3 month followupfollow-up
43211Yes506 month followupfollow-up
22222   No
22222   unknown
22222   unknown
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.