Hello Pls, can you review my formulas to help me understand the error? I have the table below: CLIENT_NAMECIDTASKTASK_ASSIGNED_ONTASK_STATUSTASK_COMPLETED_DATETASKACTUALPERFORMERCUIDClient A1000702Pre Engagement Process04-feb-22Closed18-feb-22John Smith Client B1000542Pre Engagement Process03-feb-22Closed11-feb-22Will Smith Client C1000603Pre Engagement Process02-feb-22Closed04-feb-22Jorge Fernandez Client D1000461Pre Engagement Process01-feb-22Closed18-feb-22Juan valdes Client E1000583Pre Engagement Process17-ene-22Closed17-JAN-22 John Smith Client E1000583Partner Acknowledgement - 1233528 - 1000583MY0102-feb-22Closed04-feb-22ibmbpmadmin1000583MY01Client E1000582Start Partner Engagement - 1233528 - 1000583BE0120-JAN-21Closed01-feb-22Juan valdes1000583BE01Client E1000583Start Partner Engagement - 1233528 - 1000583MY0120-JAN-22 Closed02-feb-22Juan valdes1000583MY01Client E1000583Assign Implementation Consultant - 1233528 - 1000583MY0117-JAN-22 Received Jorge Fernandez1000583MY01Client E1000583IPM HandOff17-JAN-22 Received Juan valdes Client E1000582Partner Assignment - 1233528 - 1000583BE0117-JAN-21Closed18-JAN-21John Smith1000583BE01Client E1000583Partner Assignment - 1233528 - 1000583MY0117-JAN-22 Closed18-JAN-22John Smith1000583MY01Client E1000583Resource Allocation17-JAN-22 Closed17-JAN-22ibmbpmadmin 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

Forum Discussion

romovaro

Responsive Resident

4 years ago

Solved

Review Custom formulas

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 =

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

Need Help

lbendlin

4 years ago

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

Engagement.pbix24 KB

15 Replies

lbendlin

Super User

4 years ago

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

romovaro

Responsive Resident

4 years ago

Hi Ibendin

It's the last Column in the table

lbendlin

Super User

4 years ago

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
Responsive Resident
4 years ago
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
  Super User
  4 years ago
  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
  Engagement.pbix24 KB
  - romovaro
    Responsive Resident
    4 years ago
    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"))

Forum Discussion

Review Custom formulas

15 Replies

Related content

If formula

formula

DAX Formula Help

Build custom HTML with formula

DAX Formula