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 Comple...
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.

     

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.

    • AllanBerces's avatar
      AllanBerces
      Post Prodigy
      1 year ago

      Hi Anonymous thank you very much for the reply, but what i mean is from the measure below how can i incorporate if i want to filter out trade is not equal to unknown.

      Thank you very much

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.