Need overall status based on individual

[Anonymous]

Hi Team,

Need overall status based on individual status.

PFB DATA

Job     Status

job1    completed

job2     running

job3     cancelled

job4  Completd

i need overall status like if all jobs completed then complted ,if few jobs completed and few jobs running then running,if fewjobs completd and fewjobs running and few jobs cancelled then cancelled ,if few jobs active and few jobs cancelled then cancelled.

Please help me on this

Regards,

Ravi

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

You may create a measure like below to see if it works.

Overall Status = 
var _completed = 
CALCULATE(
    DISTINCTCOUNT('PFB DATA'[Job]),
    FILTER(
        ALL('PFB DATA'),
        'PFB DATA'[Status]="completed"
    )
)
var _running =
CALCULATE(
    DISTINCTCOUNT('PFB DATA'[Job]),
    FILTER(
        ALL('PFB DATA'),
        'PFB DATA'[Status]="running"
    )
)
var _cancelled =
CALCULATE(
    DISTINCTCOUNT('PFB DATA'[Job]),
    FILTER(
        ALL('PFB DATA'),
        'PFB DATA'[Status]="cancelled"
    )
)
return
IF(
    _cancelled>0,
    "cancelled",
    IF(
        _completed = CALCULATE(DISTINCTCOUNT('PFB DATA'[Job]),ALL('PFB DATA')),
        "completed",
        "running"
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

I think you can use this measure to display the status based on your criteria:

JobStatus = if(CONTAINS(Jobs,Jobs[status], "Cancelled"), "Cancelled", if(CONTAINS(Jobs, Jobs[status], "Running"), "Running", "Completed") )

[az38]

Hi @Anonymous 

try a column

Overall = 
SWITCH(TRUE(),
CALCULATE(COUNTROWS(Table), Table[Status] = "Cancelled") > 0, "Cancelled",
CALCULATE(COUNTROWS(Table), Table[Status] = "running") > 0, "running",
"completed"
)

[Anonymous]

cureently i have data in status column like completed and cancelled for above logic i am getting two i need only cancelled

[harshnathani]

Hi @Anonymous ,

Assuming you have only these 3 status.

Try this measures

Measure 4 =

var a = SUMMARIZE('Table','Table'[Column2])

var b = COUNTrows(a)

var maxs = MAXX(a,'Table'[Column2])

var mins = MINX(a,'Table'[Column2])

Return

SWITCH(

TRUE(),

b=1 , maxs,

b=2 && maxs = "Completed" && mins = "Cancelled" , "Cancelled",

b=2 && maxs = "Running" && mins = "Completed", "Running",

b=2 && maxs = "Running" && mins = "Cancelled", "Cancelled",

"Cancelled"

)

Pull measure 4 on a card to get overall status.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[harshnathani]

@Anonymous ,

Please share sample data and a better description to your problem. 

Regards,

HN