Help the formule

[Anonymous]

Hi friends

I need help building a formula, when all WorkOrder statuses are closed for the same Case Number, it should return the status "Service Completed, Close Case.

When all or some WorkOrder Status are open. It should return with the status "Service Not Completed, Do FUP".

example below.

Thank you!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please refer to my pbix file to see if it helps you.

Create a column.

RESULT Column =
VAR _countrow =
    CALCULATE (
        COUNTROWS ( Data ),
        FILTER (
            Data,
            Data[Case number] = EARLIER ( Data[Case number] )
                && Data[Work Order status] = EARLIER ( Data[Work Order status] )
        )
    )
VAR _countcase =
    CALCULATE (
        COUNTROWS ( Data ),
        FILTER ( Data, Data[Case number] = EARLIER ( Data[Case number] ) )
    )
VAR answer_ =
    IF (
        Data[Work Order status] = "Open"
            && _countcase = _countrow,
        "Service not completed.Work order open.Do follow up.",
        IF (
            Data[Work Order status] = "Closed"
                && _countcase = _countrow,
            "Service completed.All work order colsed.Close case.",
            IF ( _countcase <> _countrow, "Service Not Completed, Do follow up", BLANK () )
        )
    )
RETURN
    answer_

If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output.

Best Regards

Community Support Team _ Polly

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

[Ashish_Mathur]

Hi,

Write this calculated column formula

Result = if(calculate(countrows(Data),filter(Data,Data[Case Number]=earlier(Data[Case Number])))=calculate(countrows(Data),filter(Data,Data[Case Number]=earlier(Data[Case Number])&&Data[Work Order status]=earlier(Data[Work Order status]))),"Service Completed, Close Case","Service Not Completed, Do FUP")

Hope this helps.