Custom Dax Calculating columns

[romovaro]

 I need to create the following custom measures but i am having issues with the formula:

Below the table with the columns I need.

Task column has a lot of different and I only need to count the ones "Pre Engagement Process" and "IPM HandOff".

CLIENT_NAME	CID	TASK	TASK_STATUS	SCHEDULED
Customer A	380206	Pre Engagement Process	Closed	18-AUG-21
Customer A	380206	IPM HandOff	Closed
Customer B	380208	Pre Engagement Process	Closed	07-jul-21
Customer B	380208	IPM HandOff	Closed	20-jul-21
Customer C	380209	Pre Engagement Process	Received
Customer D	380210	IPM HandOff	Closed	10-AUG-21
Customer D	380210	Pre Engagement Process	Closed	31-jul-21

Total Tasks Pre engagement Team. (Formula that calculates all the task with task: "Pre Engagement Process" and Task_Status "Closed & Received" and all the task : "IPM HandOff" with Task_status "Received")

Total Tasks Pre-Engagement Team = CALCULATE(COUNTROWS(CELERGO_12062021V1),Filter(CELERGO_12062021V1,CELERGO_12062021V1[TASK_STATUS] ="Received" && CELERGO_12062021V1[TASK] IN { "Pre Engagement Process", "IPM HandOff" }))   

(I just need to add in the formula that I also want "Pre Engagement Process = Closed). Tried different options but...

 Total Tasks Pre engagement Team Closed. (Formula that calculates all the task with task: "IPM HandOff" and Task_Status "Closed" and have a Scheduled Date

Total Tasks Pre-Engagement Team Closed = CALCULATE(COUNTROWS(CELERGO_12062021V1),Filter(CELERGO_12062021V1,CELERGO_12062021V1[TASK] ="IPM HandOff" && CELERGO_12062021V1[TASK_STATUS]="Closed"))

(I just need to add in the formula that I want to add the count  with a date in the Schedule column)

Thanks

[v-jingzhang · Accepted solution]

Hi @romovaro 

You can use || (OR) in the measure. 

Total Tasks Pre-Engagement Team = 
CALCULATE (
    COUNTROWS ( CELERGO_12062021V1 ),
    FILTER (
        CELERGO_12062021V1,
        ( CELERGO_12062021V1[TASK_STATUS] IN { "Received", "Closed" } && CELERGO_12062021V1[TASK] = "Pre Engagement Process" )
            || ( CELERGO_12062021V1[TASK_STATUS] = "Received" && CELERGO_12062021V1[TASK] = "IPM HandOff" )
    )
)

And use NOT(ISBLANK()) to determine whether a row in the column has a value. 

Total Tasks Pre-Engagement Team Closed = 
CALCULATE (
    COUNTROWS ( CELERGO_12062021V1 ),
    FILTER (
        CELERGO_12062021V1,
        CELERGO_12062021V1[TASK] = "IPM HandOff"
            && CELERGO_12062021V1[TASK_STATUS] = "Closed"
            && NOT ( ISBLANK ( CELERGO_12062021V1[SCHEDULED] ) )
    )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[v-jingzhang · Accepted solution]

Hi @romovaro 

You can tweak the current measures. 

Total Tasks Pre-Engagement Team =
CALCULATE (
    COUNTROWS ( CELERGO_12062021V1 ),
    FILTER (
        CELERGO_12062021V1,
        ( CELERGO_12062021V1[TASK_STATUS] IN { "Received", "Closed" }
            && CELERGO_12062021V1[TASK] = "Pre Engagement Process" )
    )
)

Total Tasks Pre-Engagement Team Closed =
CALCULATE (
    COUNTROWS ( CELERGO_12062021V1 ),
    FILTER (
        CELERGO_12062021V1,
        CELERGO_12062021V1[TASK] = "IPM HandOff"
            && CELERGO_12062021V1[TASK_STATUS] = "Closed"
    )
)

Then create a third measure

Current Workload = [Total Tasks Pre-Engagement Team] - [Total Tasks Pre-Engagement Team Closed]

Best regards,

Jing

[v-jingzhang · Accepted solution]

Hi @romovaro 

You can use || (OR) in the measure. 

Total Tasks Pre-Engagement Team = 
CALCULATE (
    COUNTROWS ( CELERGO_12062021V1 ),
    FILTER (
        CELERGO_12062021V1,
        ( CELERGO_12062021V1[TASK_STATUS] IN { "Received", "Closed" } && CELERGO_12062021V1[TASK] = "Pre Engagement Process" )
            || ( CELERGO_12062021V1[TASK_STATUS] = "Received" && CELERGO_12062021V1[TASK] = "IPM HandOff" )
    )
)

And use NOT(ISBLANK()) to determine whether a row in the column has a value. 

Total Tasks Pre-Engagement Team Closed = 
CALCULATE (
    COUNTROWS ( CELERGO_12062021V1 ),
    FILTER (
        CELERGO_12062021V1,
        CELERGO_12062021V1[TASK] = "IPM HandOff"
            && CELERGO_12062021V1[TASK_STATUS] = "Closed"
            && NOT ( ISBLANK ( CELERGO_12062021V1[SCHEDULED] ) )
    )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[romovaro]

HI v-jingzhang

it seems I would need the same formula but instead of using the "OR"..I need Minus IPM handOff Closed

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

MINUS

the IPM HandOff Tasks with status closed. (instead of Received)

How could I add the minus in the formula? Thanks

[v-jingzhang · Accepted solution]

Hi @romovaro 

You can tweak the current measures. 

Total Tasks Pre-Engagement Team =
CALCULATE (
    COUNTROWS ( CELERGO_12062021V1 ),
    FILTER (
        CELERGO_12062021V1,
        ( CELERGO_12062021V1[TASK_STATUS] IN { "Received", "Closed" }
            && CELERGO_12062021V1[TASK] = "Pre Engagement Process" )
    )
)

Total Tasks Pre-Engagement Team Closed =
CALCULATE (
    COUNTROWS ( CELERGO_12062021V1 ),
    FILTER (
        CELERGO_12062021V1,
        CELERGO_12062021V1[TASK] = "IPM HandOff"
            && CELERGO_12062021V1[TASK_STATUS] = "Closed"
    )
)

Then create a third measure

Current Workload = [Total Tasks Pre-Engagement Team] - [Total Tasks Pre-Engagement Team Closed]

Best regards,

Jing

[romovaro]

Thank you Jing.

And if I want to show the 27 current worload projects in a table? Tried different ways but i always get more than 27 (in this case)

[v-jingzhang]

Hi @romovaro 

Sorry for the late reply. The requirement is not clear. Based on the current sample data, the red 4 lines compose the first measure and the green 3 lines compose the second measure. Their difference is 1. But if you want to show the current one workload, it's not clear which one should be it?

Regards,

Jing

[romovaro]

Hi Jin

The pre engagement team starts working when Pre Engagement is "received"...when they have finished with the customer they change status to "closed"....but they are still in charge (current workload"...until IPM handOff is closed.

In the table (according to the example) the only 1 is= Customer C

[v-jingzhang · Accepted solution]

Hi @romovaro 

You can use this measure as a filter field in the table visual.

Flag =
VAR __clientsPEP =
    SELECTCOLUMNS (
        FILTER (
            ALL ( CELERGO_12062021V1 ),
            ( CELERGO_12062021V1[TASK_STATUS]
                IN { "Received", "Closed" }
                && CELERGO_12062021V1[TASK] = "Pre Engagement Process" )
        ),
        "Client", CELERGO_12062021V1[CLIENT_NAME]
    )
VAR __clientIH =
    SELECTCOLUMNS (
        FILTER (
            ALL ( CELERGO_12062021V1 ),
            CELERGO_12062021V1[TASK] = "IPM HandOff"
                && CELERGO_12062021V1[TASK_STATUS] = "Closed"
        ),
        "Client", CELERGO_12062021V1[CLIENT_NAME]
    )
VAR __current = EXCEPT ( __clientsPEP, __clientIH )
RETURN
    IF ( SELECTEDVALUE ( CELERGO_12062021V1[CLIENT_NAME] ) IN __current, 1 )

Best Regards,

Jing

[lbendlin]

Your formula already does that.  If you filter by a particular value A then you don't need to include another condition to exclude value B - that is already implicit.

AND  = all conditions apply

OR = any one condition is sufficient

By the way your "minus"  would translate to AND NOT  (if it were needed)

[romovaro]

Hi Ibendin...

THe "pre engagement team" has decided that the best value to show their performance is to calculate:

equirement 1 = Pre Engagement Process - Closed and recevied minus the IPM HandOFF already closed

I guess that using "AND NOT" is not going ot help. I need this calculation to reduce from the total (requirement 1). I need to count them in order to get "my 27" table below

[lbendlin]

Not exactly clear what your boolean statement would look like but your can use "IN"  as stand-in for "OR"

CALCULATE(COUNTROWS(CELERGO_12062021V1),CELERGO_12062021V1[TASK_STATUS] IN {"Received","Closed"},  CELERGO_12062021V1[TASK] IN { "Pre Engagement Process", "IPM HandOff" })   

, as you already did for part of it.