Grouping Names using department name + Months between 2 dates involving 2 columns

[romovaro]

HI Power Bi,

I have 2 different questions. Hope you can share some tips

First question:

1) I have a column with names. I just want to create 2 groups. If the names are the ones below, Team: Pre Engagement Team and if not, the name belongs to the Implementation Team.

Name	Team
John Smith	Pre Engagement Team
Oscar Garcia	Pre Engagement Team
Schulster, John	Pre Engagement Team
Roger James	Pre Engagement Team

2) Question

I have one column (task) and another one with dates (Task_assigned_ON2)

I need to calculate , by Client_Name or CID, the difference in months or weeks between the date when the "Pre engagement Process" begun until the Date of IPM HandOff.

CLIENT_NAME	CID	TASK	TASK_ASSIGNED_ON	TASK_ASSIGNED_ON2
Client A	380227	IPM HandOff	27-JUL-21 07.10.56.000000 PM	27-JUL-21
Client A	380227	Resource Allocation	23-JUL-21 04.38.21.000000 PM	23-JUL-21
Client A	380227	Partner Assignment - 965543 - 380227AT01	23-JUL-21 02.15.13.000000 PM	23-JUL-21
Client A	380227	Partner Assignment - 965543 - 380227TN01	23-JUL-21 02.14.47.000000 PM	23-JUL-21
Client A	380227	Partner Assignment - 965543 - 380227GR01	23-JUL-21 02.14.45.000000 PM	23-JUL-21
Client A	380227	Track Welcome Email	21-JUN-21 11.42.52.000000 AM	21-JUN-21
Client A	380227	Client System Setup	21-JUN-21 11.42.43.000000 AM	21-JUN-21
Client A	380227	Pre Engagement Process	21-JUN-21 11.42.43.000000 AM	21-JUN-21
Client A	380227	Greenlight Checks	03-JUN-21 01.47.33.000000 PM	03-JUN-21

[parry2k]

@romovaro maybe it needs a small fix, to wrap AND conditions in its own segment

Tasks IPM HandOff & Closed = 0+CALCULATE (
        DISTINCTCOUNT( CELERGO_12062021V1[CID] ),
        ( CELERGO_12062021V1[TASK] = "Pre Engagement Process" && CELERGO_12062021V1[TASK_STATUS] IN {"closed", "Received"} )
||         ( CELERGO_12062021V1[TASK] = "IPM Hand Off" && CELERGO_12062021V1[TASK_STATUS] = "Received" )
        )

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[lbendlin]

The first question is ambiguous. What have you tried and where are you stuck?

The second question can be answered with DAX.  Do you want this as a measure or is a calculated column sufficient?

[romovaro]

Hi Ibendin

For the first question I created a different excel file (Table) with the names I want to see in the report and created a relationship with the column Taskactualperformer. (that has all the names working on the process). The report needs to show only the KPI from some of them. (pre Engagement team) I would like to create a column saysing....if this name; then Pre Engagement Team , if not....else.

Tasks IPM HandOff & Closed = VAR result =

    CALCULATE (

        DISTINCTCOUNT( CELERGO_12062021V1[CID] ),

        FILTER (

            CELERGO_12062021V1,

            CELERGO_12062021V1[TASK]

                IN { "Pre Engagement Process" , "Pre Engagement Process" , "IPM HandOff" }

                && CELERGO_12062021V1[TASK_STATUS] IN {"Received", "closed", "Received"}

        )

    )

RETURN

    IF ( ISBLANK ( result ), 0, result )

Regarding the formula above...What I need to show is all the tasks that have

Task= Pre Engagement Process and Task_Status="Closed or Received"  and

Task = IPM Hand Off and Task_Status= "Received"

with this formula I get 127. (But in the excel file I have 127 Pre engagement process closed/Received" and 18 IPM HandOff received....somethin's missing.

and then, once formula is fixed tHe idea is to add...and only for the "Pre-engagement team"...

2) At the end, what managament wants to see if the number of tasks 

How many Task = Pre Engagement Process were task_Status="Received" first day of the month and How many Task=pre Engagement process were Task_Status = closed last day of the month.

Calculated column or value is ok 

[lbendlin]

1.

Tasks IPM HandOff & Closed = 0+CALCULATE (
        DISTINCTCOUNT( CELERGO_12062021V1[CID] ),
        CELERGO_12062021V1[TASK] = "Pre Engagement Process" && CELERGO_12062021V1[TASK_STATUS] IN {"closed", "Received"}
||         CELERGO_12062021V1[TASK] = "IPM Hand Off" && CELERGO_12062021V1[TASK_STATUS] = "Received"
        )

[romovaro]

Hi Ibendin

thanks. I my excel file i have 106 Pre engagement process (closed or Received) and 4 IPM HandOff with status Received.... 

Just noticed that CID is the same in Pre engagement process than IPM handOff, therefore distinctcount cannot be used. 

I used the formula below with your help only using COUNT...but i get the same number 106 (still missing the 4 IPM i guess...)

Maybe the correct formula is using the "OR" instead of &&?

Task= Pre Engagement Process and Task_Status="Closed or Received"   OR

Task = IPM Hand Off and Task_Status= "Received"

COuld you update the formula using or requiremtn 1 "Task= Pre Engagement Process and Task_Status="Closed or Received" " or Requirement 2  "Task = IPM Hand Off and Task_Status= "Received""...please?

all task = 0+CALCULATE (

COUNT( 'Celergo-PreEngTeam'[CID] ),

'Celergo-PreEngTeam'[TASK] = "Pre Engagement Process" && 'Celergo-PreEngTeam'[TASK_STATUS] IN {"closed", "Received"}

OR

'Celergo-PreEngTeam'[TASK] = "IPM Hand Off" && 'Celergo-PreEngTeam'[TASK_STATUS] = "Received"

)

CLIENT_NAME	CID	INSTANCE_NUMBER	TASK	TASK2	TASK_ASSIGNED_ON	TASK_ASSIGNED_ON2	TASK_STATUS
Client A	380310	1122324	Start Partner Engagement - 1107560 - 380310ES01	Start Partner Engagement - 1107560 - 380310ES01	23-NOV-21 05.45.08.000000 PM	23-NOV-21	Closed
Client A	380310	1107560	IPM HandOff	IPM HandOff	18-NOV-21 01.06.40.000000 PM	18-NOV-21	Received
Client A	380310	1122323	Start Partner Engagement - 1107560 - 380310CA01	Start Partner Engagement - 1107560 - 380310CA01	18-NOV-21 01.06.40.000000 PM	18-NOV-21	Closed
Pacaso	380310	1107560	Pre Engagement Process	Pre Engagement Process	01-OCT-21 02.52.09.000000 PM	01-OCT-21	Closed

[lbendlin · Accepted solution]

If you look at my proposal you will notice the " || " in the code - that is DAX speak for "OR",  meaning my proposal already implemented the logic you want to achieve. 

[romovaro]

HI Ibendlin,

it seems I would need the same formula but instead of using the "OR"..

I need to provide the total of "Pre Engagement Process - Closed and recevied) like your formula does...

all task = 0+CALCULATE (

COUNT('Celergo-PreEngTeam'[CID] ),

'Celergo-PreEngTeam'[TASK] = "Pre Engagement Process" && 'Celergo-PreEngTeam'[TASK_STATUS] IN {"Closed", "Received"}

MINUS

the IPM HandOff Tasks with status closed.

'Celergo-PreEngTeam'[TASK] = "IPM Hand Off" && 'Celergo-PreEngTeam'[TASK_STATUS] = "Closed"

[romovaro]

thanks Ibendin for your help. THe formula works. I guess I need to check what happens with the missing 4...