Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Change Status base from Progress

Hi and good day,

Can someone help me on my table on how to achieved my desired output. Basically in one ID let say Room01.
     a. if the Trade Painting or D. Scaff is lessthan 100 = Partial Completed 
     b. if one of the Trade is started  = On- going
     c.  if none of the Trade started = Not Started
     d. if all the Trade in ID are 100 = Completed

Thank you

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi AllanBerces ,

    Perhaps the following expression:

    Measure = var _D = CALCULATE(MAX('Table'[Work%]),FILTER(ALLEXCEPT('Table','Table'[ID]),[Trade]="D.Scaff"))
var _P = CALCULATE(MAX('Table'[Work%]),FILTER(ALLEXCEPT('Table','Table'[ID]),[Trade]="Painting"))
var _Start = CALCULATE(AVERAGE('Table'[Work%]),ALLEXCEPT('Table','Table'[ID]))
var _Unkonwn = IF( NOT MAX('Table'[Trade]) IN VALUES('Table'[Trade]),1,0)
RETURN SWITCH(TRUE(),
_P>10&&_P<100&&_Start<>100,"Partial Completed",
_D>10&&_D<100&&_Start<>100,"Partial Completed",
_Start>0&&_Start<>100,"On-going",
_Start=0,"Not Started",
_Start=100,"Completed",
_Unkonwn=1,"Unknown"
)

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

     

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi AllanBerces ,

    With the current logic, each room is not fully adapted to the conditions, please try more restrictions to adjust:

    Measure = var _D = CALCULATE(MAX('Table'[Work%]),FILTER(ALLEXCEPT('Table','Table'[ID]),[Trade]="D.Scaff"))
var _P = CALCULATE(MAX('Table'[Work%]),FILTER(ALLEXCEPT('Table','Table'[ID]),[Trade]="Painting"))
var _Start = CALCULATE(AVERAGE('Table'[Work%]),ALLEXCEPT('Table','Table'[ID]))
RETURN SWITCH(TRUE(),
_P>10&&_P<100&&_Start<>100,"Partial Completed",
_D>10&&_D<100&&_Start<>100,"Partial Completed",
_Start>0&&_Start<>100,"On-going",
_Start=0,"Not Started",
_Start=100,"Completed"
)

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

    • AllanBerces's avatar
      AllanBerces
      Post Prodigy
      1 year ago

      Hi Anonymous thank you for the reply, just one question how can add if i want to filter Trade is not equal to unknown. 

       

      Thank you

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi AllanBerces ,

         

        You can try the following expression:

        Measure 2 = IF( NOT MAX('Table'[Trade]) IN VALUES('Table'[Trade]),1,0)

        A 1 represents an item that is not in the original trade, and a 0 represents an item that is present.

         

        Hope it helps!

         

        Best regards,
        Community Support Team_ Scott Chang

         

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