Review Custom formulas

[romovaro]

Hello

Pls, can you review my formulas to help me understand the error?

I have the table below:

CLIENT_NAME	CID	TASK	TASK_ASSIGNED_ON	TASK_STATUS	TASK_COMPLETED_DATE	TASKACTUALPERFORMER	CUID
Client A	1000702	Pre Engagement Process	04-feb-22	Closed	18-feb-22	John Smith
Client B	1000542	Pre Engagement Process	03-feb-22	Closed	11-feb-22	Will Smith
Client C	1000603	Pre Engagement Process	02-feb-22	Closed	04-feb-22	Jorge Fernandez
Client D	1000461	Pre Engagement Process	01-feb-22	Closed	18-feb-22	Juan valdes
Client E	1000583	Pre Engagement Process	17-ene-22	Closed	17-JAN-22	John Smith
Client E	1000583	Partner Acknowledgement - 1233528 - 1000583MY01	02-feb-22	Closed	04-feb-22	ibmbpmadmin	1000583MY01
Client E	1000582	Start Partner Engagement - 1233528 - 1000583BE01	20-JAN-21	Closed	01-feb-22	Juan valdes	1000583BE01
Client E	1000583	Start Partner Engagement - 1233528 - 1000583MY01	20-JAN-22	Closed	02-feb-22	Juan valdes	1000583MY01
Client E	1000583	Assign Implementation Consultant - 1233528 - 1000583MY01	17-JAN-22	Received		Jorge Fernandez	1000583MY01
Client E	1000583	IPM HandOff	17-JAN-22	Received		Juan valdes
Client E	1000582	Partner Assignment - 1233528 - 1000583BE01	17-JAN-21	Closed	18-JAN-21	John Smith	1000583BE01
Client E	1000583	Partner Assignment - 1233528 - 1000583MY01	17-JAN-22	Closed	18-JAN-22	John Smith	1000583MY01
Client E	1000583	Resource Allocation	17-JAN-22	Closed	17-JAN-22	ibmbpmadmin

Where Task column can contain different tasks:

- Pre engagement Process

- Start partner Engagement

- Partner Acknowledgement 

- etc.

And I need to calculate the statetements below:

Total to Book:  Total number of CUIDS, where pre-engagement task is closed by Pre team member

 In the table, When Task = “Pre Engagement Process” and Task_status = Closed there is no CUID (unique customer number). I need to use the CID column and then Intersect with the CUID column (IN the table above, CID 1000583 has 2 different CUID. Blank space do not count)

I tried the formula below but something is wrong:

Total To Book2 =

var tab =

    CALCULATETABLE(

        VALUES('BPMS LOF Report'[CID]),

        FILTER (

            'BPMS LOF Report',

            'BPMS LOF Report'[TASK] = "Pre Engagement Process"

                && 'BPMS LOF Report'[TASK_STATUS]= "Closed"

                 && 'BPMS LOF Report'[TASKACTUALPERFORMER] = "John Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Will Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Juan Valdes"

    )

    return

    CALCULATE(

        DISTINCTCOUNT('BPMS LOF Report'[CUID]),

        INTERSECT(VALUES('BPMS LOF Report'[CID]), tab),

        FILTER (

        'BPMS LOF Report',

        CONTAINSSTRING ( 'BPMS LOF Report'[TASK], "Pre Engagement" )

            && 'BPMS LOF Report'[TASK_STATUS] = "Closed"

    )

)

Entities booked: total number CUIDs where prep team closed "start partner engagement" task and "Partner Ackn" is closed

I tried the formula below but something is wrong:

Entities Booked =

VAR MeetsFirstRequirement =

    CALCULATETABLE(

        VALUES('BPMS LOF Report'[CUID] ),

        CONTAINSSTRING(

            'BPMS LOF Report'[TASK],

            "Start Partner Engagement"

            && 'BPMS LOF Report'[TASK_STATUS] = "Closed"

        )

                 && 'BPMS LOF Report'[TASKACTUALPERFORMER] = "John Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Will Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Juan Valdes"

    )

VAR MeetsBothRequirements =

    CALCULATETABLE(

        VALUES( 'BPMS LOF Report'[CUID] ),

        MeetsFirstRequirement,

        'BPMS LOF Report'[TASK_STATUS] = "Closed",

        CONTAINSSTRING(

            'BPMS LOF Report'[TASK],

            "Partner Acknowledgement"

        )

    )

RETURN MeetsBothRequirements 

Entities to be confirmed: total number CUIDs here prep team closed start partner engagement task and Partner Ackn is not closed

(Same as the formula above but instead of Partner Acknoledgement Closed is Received.

I tried the formula below but something is wrong:

Entities to be confirmed =

VAR MeetsFirstRequirement =

VAR MeetsFirstRequirement =

    CALCULATETABLE(

        VALUES('BPMS LOF Report'[CUID] ),

        CONTAINSSTRING(

            'BPMS LOF Report'[TASK],

            "Start Partner Engagement"

            && 'BPMS LOF Report'[TASK_STATUS] = "Closed"

        )

                 && 'BPMS LOF Report'[TASKACTUALPERFORMER] = "John Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Will Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Juan Valdes"

    )

VAR MeetsBothRequirements =

    CALCULATETABLE(

        VALUES( 'BPMS LOF Report'[CUID] ),

        MeetsFirstRequirement,

        'BPMS LOF Report'[TASK_STATUS] = "Received",

        CONTAINSSTRING(

            'BPMS LOF Report'[TASK],

            "Partner Acknowledgement"

        )

    )

RETURN MeetsBothRequirements 

Could you offer any help with the formulas?

thanks

[romovaro]

Hi Ibendin

Trying to write your code but unsuccesful. Sorry but my coding is not so good.

Which formula from the 2 above is better?

In the example below, I need to add the "containsstring" in the task because the name changes all the time...but the string "Partner Engagement" or "Partner acknowledgement" remains the same.

formula 1)

2Entities Booked =

var tab =CALCULATETABLE (VALUES ( 'BPMS LOF Report'[CUID] ),

FILTER (

'BPMS LOF Report',

'BPMS LOF Report'[TASK_STATUS] = "Closed"

&& CONTAINSSTRING ( 'BPMS LOF Report'[TASK], "Partner Engagement" )

)

&& 'BPMS LOF Report'[TASKACTUALPERFORMER] in {"John Smith","Will Smith","Juan valdes"})

return CALCULATE (COUNTROWS('BPMS LOF Report'),

INTERSECT ( VALUES('BPMS LOF Report'[CUID]), tab ),

FILTER (

'BPMS LOF Report',

'BPMS LOF Report'[TASK_STATUS] = "Closed"

&& CONTAINSSTRING ( 'BPMS LOF Report'[TASK], "Partner Acknowledgement")

)

)

Formula 2) If I removed the && from the formula like your i get an error..

Entities Booked =

VAR MeetsFirstRequirement =

    CALCULATETABLE(

        VALUES('BPMS LOF Report'[CUID] ),

        CONTAINSSTRING(

            'BPMS LOF Report'[TASK],

            "Start Partner Engagement"

            && 'BPMS LOF Report'[TASK_STATUS] = "Closed"

        )

                 && 'BPMS LOF Report'[TASKACTUALPERFORMER] = "John Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Will Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Juan Valdes"

    )

VAR MeetsBothRequirements =

    CALCULATETABLE(

        VALUES( 'BPMS LOF Report'[CUID] ),

        MeetsFirstRequirement,

        'BPMS LOF Report'[TASK_STATUS] = "Closed",

        CONTAINSSTRING(

            'BPMS LOF Report'[TASK],

            "Partner Acknowledgement"

        )

    )

RETURN MeetsBothRequirements 

[lbendlin · Accepted solution]

Now written as measures:

Total To Book = 
var tab=CALCULATETABLE(values('BPMS LOF Report'[CID]),
ALL('BPMS LOF Report'),
'BPMS LOF Report'[TASK]="Pre Engagement Process",
'BPMS LOF Report'[TASK_STATUS]="Closed",
'BPMS LOF Report'[TASKACTUALPERFORMER] in {"John Smith","Will Smith","Juan Valdes"})
return calculate(COUNTROWS(values('BPMS LOF Report'[CUID])),all('BPMS LOF Report'),'BPMS LOF Report'[CID] in tab)

Entities Booked = 
calculate(COUNTROWS(values('BPMS LOF Report'[CUID])),
       all('BPMS LOF Report'),
       'BPMS LOF Report'[TASKACTUALPERFORMER] in {"John Smith","Will Smith","Juan Valdes"},
       'BPMS LOF Report'[TASK_STATUS] = "Closed",
       CONTAINSSTRING('BPMS LOF Report'[TASK],"Start Partner Engagement") || CONTAINSSTRING('BPMS LOF Report'[TASK],"Partner Acknowledgement"))

Entities TBC = 
calculate(COUNTROWS(values('BPMS LOF Report'[CUID])),
       all('BPMS LOF Report'),
       'BPMS LOF Report'[TASKACTUALPERFORMER] in {"John Smith","Will Smith","Juan Valdes"},
       'BPMS LOF Report'[TASK_STATUS] = "Closed" && CONTAINSSTRING('BPMS LOF Report'[TASK],"Start Partner Engagement") 
          || 'BPMS LOF Report'[TASK_STATUS] = "Received" && CONTAINSSTRING('BPMS LOF Report'[TASK],"Partner Acknowledgement"))

see attached

[romovaro]

Thank you very much Ibendin. It works.

Just a quick change For Entities to be confirmed I need to have

TASK = Start Partner Engagement and Task_Status = Closed by the Pre engagement Team

&&

TASK= Partner acknowledgement and Task_Status=Received

I tried to update your formula adding this extra field but not working. Could you have a look below at the 2 formulas below?

Entities TBC2 =

calculate(COUNTROWS(values('BPMS LOF Report'[CUID])),

all('BPMS LOF Report'),

'BPMS LOF Report'[TASKACTUALPERFORMER] in {{"Will Smith","John SMith","Juan valdes"},

'BPMS LOF Report'[TASK_STATUS] IN {"Closed","Received"},

CONTAINSSTRING ('BPMS LOF Report'[TASK] IN {"Start Partner Engagement","Partner Acknowledgement"}

Entities TBC3 =

calculate(COUNTROWS(values('BPMS LOF Report'[CUID])),

all('BPMS LOF Report'),

'BPMS LOF Report'[TASKACTUALPERFORMER] in {"Will Smith","John SMith","Juan valdes"},

'BPMS LOF Report'[TASK_STATUS] = "closed",

CONTAINSSTRING('BPMS LOF Report'[TASK],"Start Partner Engagement") && 'BPMS LOF Report'[TASK_STATUS] = "Received",CONTAINSSTRING('BPMS LOF Report'[TASK],"Partner Acknowledgement"))

[lbendlin]

"For Entities to be confirmed I need to have

TASK = Start Partner Engagement and Task_Status = Closed by the Pre engagement Team

&&

TASK= Partner acknowledgement and Task_Status=Received"

That makes no logical sense.  Did you mean to say 

TASK = Start Partner Engagement && Task_Status = Closed by the Pre engagement Team

||

TASK= Partner acknowledgement && Task_Status=Received

[romovaro]

Hi IBendin

Sorry for my explanations. Customer goes through diferent stages,.."Pre engagement Process"...... "Start Partner engagement"...."Partner acknowledgement"...etc.

the pre engagement team for this example is: WIll Smith, John SMith and Juan Valdes.

Entities to be booked: total of CUID where the task containing "Start Partner engagement" was closed (Task_status = Closed) by the pre engagement team (TASKACTUALPERFORMER=WIll Smith, John SMith and Juan Valdes) and the task partner acknowledgement is Closed.

NOTE: (Doesn't matter is the task "Partner acknoeldgement" was closed by the pre engagement team or not...just needs to show CLosed in the Task_Status

Entities to be confirmed: total of CUID where the task containing "Start Partner engagement" was closed (Task_status = Closed) by the pre engagement team (TASKACTUALPERFORMER=WIll Smith, John SMith and Juan Valdes) and the task partner acknowledgement is Received.

NOTE: (Doesn't matter is the task "Partner acknoeldgement" is received by the pre engagement team or not...just needs to show Received in the Task_Status

[lbendlin]

NOTE: (Doesn't matter is the task "Partner acknoeldgement" was closed by the pre engagement team or not...just needs to show CLosed in the Task_Status

If it doesn't matter then the logic check should not be included.

"if A doesn't matter and B = 1"    is the same as  "if B = 1".

You may want to spend time cleaning up your logic rules.

[romovaro]

Hi Ibendin

I think both A and B matters. I think the formula I used at the beg of the post explains the requirements.

FIrst requirement is: total of CUID where the task containing "Start Partner engagement" was closed (Task_status = Closed) by the pre engagement team (TASKACTUALPERFORMER=WIll Smith, John SMith and Juan Valdes) (that should be the part in the formula highlighted in red)

Second requirement: Once we have all the CUIDs from n the first requirement, we check the CUID column to see CUIDs with the task "Partner acknoledgement" Closed (that should be the part in the formula highlighted in blue)

I beleive my formula is missing something after....RETURN MeetsBothRequirements 

Entities Booked =

VAR MeetsFirstRequirement =

    CALCULATETABLE(

        VALUES('BPMS LOF Report'[CUID] ),

        CONTAINSSTRING(

            'BPMS LOF Report'[TASK],

            "Start Partner Engagement"

            && 'BPMS LOF Report'[TASK_STATUS] = "Closed"

        )

            && 'BPMS LOF Report'[TASKACTUALPERFORMER] = "John Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Will Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Juan Valdes"

    )

VAR MeetsBothRequirements =

    CALCULATETABLE(

        VALUES( 'BPMS LOF Report'[CUID] ),

        MeetsFirstRequirement,

        'BPMS LOF Report'[TASK_STATUS] = "Closed",

        CONTAINSSTRING(

            'BPMS LOF Report'[TASK],

            "Partner Acknowledgement"

        )

    )

RETURN MeetsBothRequirements 

Hope this explains better the situation.  

[lbendlin]

Your sample does not contain a CUID column or table. Please update/expand. 

[romovaro]

Hi Ibendin

It's the last Column in the table

[lbendlin]

Ah, sorry, it didn't show on my browser. 

 "I need to use the CID column and then Intersect with the CUID column" is not very clear to me.

What is the expected outcome based on the sample data?

This piece of code is likely incorrect:

 FILTER (

            'BPMS LOF Report',

            'BPMS LOF Report'[TASK] = "Pre Engagement Process"

                && 'BPMS LOF Report'[TASK_STATUS]= "Closed"

                 && 'BPMS LOF Report'[TASKACTUALPERFORMER] = "John Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Will Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Juan Valdes"

    )

It should likely be 

 FILTER (

            'BPMS LOF Report',

            'BPMS LOF Report'[TASK] = "Pre Engagement Process"

                && 'BPMS LOF Report'[TASK_STATUS]= "Closed"

                 && ('BPMS LOF Report'[TASKACTUALPERFORMER] = "John Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Will Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Juan Valdes")

    )

Remember that AND operations have a higher order than OR operations.

You can also rewrite the variable like this:

var tab =   CALCULATETABLE(
        VALUES('BPMS LOF Report'[CID]),
        'BPMS LOF Report'[TASK] = "Pre Engagement Process",
        'BPMS LOF Report'[TASK_STATUS]= "Closed",
        'BPMS LOF Report'[TASKACTUALPERFORMER] IN { "John Smith", "Will Smith","Juan Valdes"}
    )

[romovaro]

Hi Ibendin

During the "Pre engagement process" , CUID are not yet assigned. Only CID number. 

CID is a unique customer number and CUID is used as entity level. (CID could be Microsoft and CUID is Microsoft Spain, Microsoft Germany, Microsoft Colombia, etc.)

What I am trying to acheive with the formula is...

1 step= Find all the CID that have Task = Pre Engagement Process // Task_status=Closed and Taskactualperformer = John Smith, Will SMith or Juan Valdes

Once I have this requirement , I want the formula to return the CUID that have CID = 1 step

Example in the table above:

CID 1000583 has pre Engagement Process Closed and performer is John SMith (requirements checked)

return: IF we check CID column and intersect with the CUID column, we see that CID 1000583 have unique CUIDs (trying to use distinccount in the formula and blank not included) = 

1000583MY01

1000583BE01

Formula That i tried:

Total To Book2 =

var tab =

    CALCULATETABLE(

        VALUES('BPMS LOF Report'[CID]),

        FILTER (

            'BPMS LOF Report',

            'BPMS LOF Report'[TASK] = "Pre Engagement Process"

                && 'BPMS LOF Report'[TASK_STATUS]= "Closed"

                 && 'BPMS LOF Report'[TASKACTUALPERFORMER] = "John Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Will Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Juan Valdes"

    )

    return

    CALCULATE(

        DISTINCTCOUNT('BPMS LOF Report'[CUID]),

        INTERSECT(VALUES('BPMS LOF Report'[CID]), tab),

        FILTER (

        'BPMS LOF Report',

        CONTAINSSTRING ( 'BPMS LOF Report'[TASK], "Pre Engagement" )

            && 'BPMS LOF Report'[TASK_STATUS] = "Closed"

    )

)

[lbendlin]

Here is a calculated column for Total to Book. Let me know if this is what you are going for.

Total to Book = 
var tab=CALCULATETABLE(values('BPMS LOF Report'[CID]),
ALL('BPMS LOF Report'),
'BPMS LOF Report'[TASK]="Pre Engagement Process",
'BPMS LOF Report'[TASK_STATUS]="Closed",
'BPMS LOF Report'[TASKACTUALPERFORMER] in {"John Smith","Will Smith","Juan Valdes"})
return calculate(COUNTROWS(values('BPMS LOF Report'[CUID])),all('BPMS LOF Report'),tab)

Sample attached.

[romovaro]

Hello Ibendlin,

thanks for your help. Just quick question regarding your formula...I see that your "To Book Column" is 26 with the Sample in the report. But unique CUID are only 2.

To count unique CUID values and "not blank" i updated your formula with the "DISTINCTCOUNTNOBLANK formula. what do you think?

Total To Book = var tab =CALCULATETABLE(values('BPMS LOF Report'[CID]), ALL('BPMS LOF Report'), 'BPMS LOF Report'[TASK]="Pre Engagement Process", 'BPMS LOF Report'[TASK_STATUS]="Closed", 'BPMS LOF Report'[TASKACTUALPERFORMER] in {"John Smith", "Will Smith", "Juan Valdes"}) return calculate(DISTINCTCOUNTNOBLANK(('BPMS LOF Report'[CUID])),all('BPMS LOF Report'),'BPMS LOF Report'[CID] in tab)

Could you help me with the other 2 pls? It's the same formula...one when task that contains "Partner acknoledgement" and is Closed and the other one when contains "Partner acknoledgement" and is Received

Entities booked: total number CUIDs where prep team closed "start partner engagement" task and "Partner Ackn" is closed

I tried the formula below but something is wrong. I guess is the last part "RETURN MeetsBothRequirements "...

Entities Booked =

VAR MeetsFirstRequirement =

    CALCULATETABLE(

        VALUES('BPMS LOF Report'[CUID] ),

        CONTAINSSTRING(

            'BPMS LOF Report'[TASK],

            "Start Partner Engagement"

            && 'BPMS LOF Report'[TASK_STATUS] = "Closed"

        )

                 && 'BPMS LOF Report'[TASKACTUALPERFORMER] = "John Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Will Smith"

            || 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Juan Valdes"

    )

VAR MeetsBothRequirements =

    CALCULATETABLE(

        VALUES( 'BPMS LOF Report'[CUID] ),

        MeetsFirstRequirement,

        'BPMS LOF Report'[TASK_STATUS] = "Closed",

        CONTAINSSTRING(

            'BPMS LOF Report'[TASK],

            "Partner Acknowledgement"

        )

    )

RETURN MeetsBothRequirements 

[romovaro]

Another try in the formula.

But I get the error: A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Entities Booked =

var tab =

CALCULATETABLE (

VALUES ( 'BPMS LOF Report'[CUID] ),

FILTER (

'BPMS LOF Report',

'BPMS LOF Report'[TASK_STATUS] = "Closed"

&& CONTAINSSTRING ( 'BPMS LOF Report'[TASK], "Partner Engagement" )

)

&& 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Will Smith"

|| 'BPMS LOF Report'[TASKACTUALPERFORMER] = "John Smith"

|| 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Juan valdes"

)

return

CALCULATE (

COUNT ( 'BPMS LOF Report'[CUID]),

INTERSECT ( VALUES('BPMS LOF Report'[CUID]), tab ),

FILTER (

'BPMS LOF Report',

'BPMS LOF Report'[TASK_STATUS] = "Closed"

&& CONTAINSSTRING ( 'BPMS LOF Report'[TASK], "Partner Acknowledgement")

)

)

[lbendlin]

A couple of notes:

- I mentioned that my proposal used calculated columns. If you look at the raw data you will see that the result is 2. Change to a measure or use your aggregation as needed.

- please check again how I wrote the code. It avoids the exact issue you are facing with the other two queries. Rewrite them accordingly.