Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help the formule

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!

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.