Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Switch statement - Multiple expressions

I have a table with columns as mentioned below

 

 

 

 

I wrote a query to get the single status for the project  but doesn't seem to work. Please let me know if there is a workaround for this

 

switch(


(([WB ID] = "1.1" And [% Complete] = 100) And
([WB ID] = "1.2" And [% Complete] <> 100) And
([WB ID] = "2" And [% Complete] <> 100) And
([WB ID] = "3" And [% Complete] <> 100) And
([WB ID] = "4" And [% Complete] <> 100) And
([WB ID] = "5" And [% Complete] <> 100)), "Customer Notified",

 

(([WB ID] = "1" And [% Complete] = 100) And
([WB ID] = "2" And [% Complete] = 100) And
([WB ID] = "3.1" And [% Complete] = 100) And
([WB ID] = "3.2" And [% Complete] = 100) And
([WB ID] = "3.3.1" And [% Complete] = 100) And
([WB ID] = "3.3.2" And [% Complete] <> 100) And
([WB ID] = "3.3.3" And [% Complete] <> 100) And
([WB ID] = "3.3.4" And [% Complete] <> 100) And
([WB ID] = "3.3.5" And [% Complete] <> 100) And
([WB ID] = "3.4" And [% Complete] <> 100) And
([WB ID] = "3.5" And [% Complete] <> 100) And
([WB ID] = "4" And [% Complete] <> 100) And
([WB ID] = "5" And [% Complete] <> 100)), "Letter Sent",

 

(([WB ID] = "1" And [% Complete] = 100) And
([WB ID] = "2" And [% Complete] = 100) And
([WB ID] = "3.1" And [% Complete] = 100) And
([WB ID] = "3.2" And [% Complete] = 100) And
([WB ID] = "3.3" And [% Complete] = 100) And
([WB ID] = "3.4.1" And [% Complete] = 100) And
([WB ID] = "3.4.2" And [% Complete] <> 100) And
([WB ID] = "3.5" And [% Complete] <> 100) And
([WB ID] = "4" And [% Complete] <> 100)), "Service Disconnected",

 

(([WB ID] = "1" And [% Complete] = 100) And
([WB ID] = "2" And [% Complete] = 100) And
([WB ID] = "3.1" And [% Complete] = 100) And
([WB ID] = "3.2" And [% Complete] = 100) And
([WB ID] = "3.3" And [% Complete] = 100) And
([WB ID] = "3.4" And [% Complete] = 100) And
([WB ID] = "3.5.1" And [% Complete] = 100) And
([WB ID] = "3.5.2" And [% Complete] = 100) And
([WB ID] = "4" And [% Complete] <> 100) And
([WB ID] = "5" And [% Complete] = 100)), "Restored After Disconnect",

 

(([WB ID] = "1" And [% Complete] = 100) And
([WB ID] = "2" And [% Complete] = 100) And
([WB ID] = "3.1" And [% Complete] = 100) And
([WB ID] = "3.2" And [% Complete] = 100) And
([WB ID] = "5" And [% Complete] = 100) And
([WB ID] = "3.3" And [% Complete] <> 100) And
([WB ID] = "3.4" And [% Complete] <> 100) And
([WB ID] = "3.5" And [% Complete] <> 100) And
([WB ID] = "4" And [% Complete] <> 100)), "Resolved Before Letter",

 

(([WB ID] = "1" And [% Complete] = 100) And
([WB ID] = "2" And [% Complete] = 100) And
([WB ID] = "3.1" And [% Complete] = 100) And
([WB ID] = "3.2" And [% Complete] = 100) And
([WB ID] = "5" And [% Complete] = 100) And
([WB ID] = "3.3" And [% Complete] = 100) And
([WB ID] = "3.4" And [% Complete] <> 100) And
([WB ID] = "3.5" And [% Complete] <> 100) And
([WB ID] = "4" And [% Complete] <> 100)), "Resolved After Letter")

 

General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

    Create a measure and use the below formula.Replace <<Table Name>> with the table name in your model

    SWITCH(TRUE(),
    AND(
         (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1.1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1.2",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))=0)
       ) ,"Customer Notified",

    AND(
         (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.2",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.3",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.4",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.5",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))=0)
       ) ,"Letter Sent",
    AND(
         (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4.1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4.2",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
       ) ,"Service Disconnected",

    AND(
         (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5.1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5.2",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))>0)
       ) ,"Restored After Disconnect", 

    AND(
         (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
       ) ,"Resolved Before Letter", 
    AND(
         (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
       ) ,"Resolved After Letter", 
    ,"NA")

  • Anonymous's avatar
    Anonymous
    8 years ago

    Anonymous Your solution works with a slight modification but thanks for the solution. Right now the issue I am facing is how to display the measure. I need to populate the projects based on the status like Below

    Project Status                      Project Count

    Customer Notified              10

    Resolved Before Letter        5

    Letter Sent                           4

    But when I try to display the measure in a visual it just populates in the tooltips instead of values as I wanted. Is there a way to create another measure based on this measure? Please let me know if you have a solution.

     

    Thanks

    Sridhar

     

    P.S The modified version of your solution that worked for me is below

     

    SWITCH(TRUE(),
    AND(
         (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1.1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1.2",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))=0)
       ) ,"Customer Notified",

    AND(
         (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.1",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.2",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.3",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.4",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.5",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))=0)
       ) ,"Letter Sent",
    AND(
         (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4.1",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4.2",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
       ) ,"Service Disconnected",

    AND(
         (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
       &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
       &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5.1",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5.2",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))>0)
       ) ,"Restored After Disconnect", 

    AND(
         (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
       ) ,"Resolved Before Letter", 
    AND(
         (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
        ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))>0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))>0)
      &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
        &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
       ) ,"Resolved After Letter", 
    ,"NA")

7 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Could you try the formula below to see if it works in your scenario? :smileyhappy:

    =
SWITCH (
    TRUE (),
    ( ( [WB ID] = "1.1"
        && [% Complete] = 100 )
        && ( [WB ID] = "1.2"
        && [% Complete] <> 100 )
        && ( [WB ID] = "2"
        && [% Complete] <> 100 )
        && ( [WB ID] = "3"
        && [% Complete] <> 100 )
        && ( [WB ID] = "4"
        && [% Complete] <> 100 )
        && ( [WB ID] = "5"
        && [% Complete] <> 100 ) ), "Customer Notified",
    ( ( [WB ID] = "1"
        && [% Complete] = 100 )
        && ( [WB ID] = "2"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.1"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.2"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.3.1"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.3.2"
        && [% Complete] <> 100 )
        && ( [WB ID] = "3.3.3"
        && [% Complete] <> 100 )
        && ( [WB ID] = "3.3.4"
        && [% Complete] <> 100 )
        && ( [WB ID] = "3.3.5"
        && [% Complete] <> 100 )
        && ( [WB ID] = "3.4"
        && [% Complete] <> 100 )
        && ( [WB ID] = "3.5"
        && [% Complete] <> 100 )
        && ( [WB ID] = "4"
        && [% Complete] <> 100 )
        && ( [WB ID] = "5"
        && [% Complete] <> 100 ) ), "Letter Sent",
    ( ( [WB ID] = "1"
        && [% Complete] = 100 )
        && ( [WB ID] = "2"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.1"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.2"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.3"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.4.1"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.4.2"
        && [% Complete] <> 100 )
        && ( [WB ID] = "3.5"
        && [% Complete] <> 100 )
        && ( [WB ID] = "4"
        && [% Complete] <> 100 ) ), "Service Disconnected",
    ( ( [WB ID] = "1"
        && [% Complete] = 100 )
        && ( [WB ID] = "2"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.1"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.2"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.3"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.4"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.5.1"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.5.2"
        && [% Complete] = 100 )
        && ( [WB ID] = "4"
        && [% Complete] <> 100 )
        && ( [WB ID] = "5"
        && [% Complete] = 100 ) ), "Restored After Disconnect",
    ( ( [WB ID] = "1"
        && [% Complete] = 100 )
        && ( [WB ID] = "2"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.1"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.2"
        && [% Complete] = 100 )
        && ( [WB ID] = "5"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.3"
        && [% Complete] <> 100 )
        && ( [WB ID] = "3.4"
        && [% Complete] <> 100 )
        && ( [WB ID] = "3.5"
        && [% Complete] <> 100 )
        && ( [WB ID] = "4"
        && [% Complete] <> 100 ) ), "Resolved Before Letter",
    ( ( [WB ID] = "1"
        && [% Complete] = 100 )
        && ( [WB ID] = "2"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.1"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.2"
        && [% Complete] = 100 )
        && ( [WB ID] = "5"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.3"
        && [% Complete] = 100 )
        && ( [WB ID] = "3.4"
        && [% Complete] <> 100 )
        && ( [WB ID] = "3.5"
        && [% Complete] <> 100 )
        && ( [WB ID] = "4"
        && [% Complete] <> 100 ) ), "Resolved After Letter"
)

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Create a measure and use the below formula.Replace <<Table Name>> with the table name in your model

      SWITCH(TRUE(),
      AND(
           (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1.1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1.2",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))=0)
         ) ,"Customer Notified",

      AND(
           (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.2",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.3",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.4",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.5",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))=0)
         ) ,"Letter Sent",
      AND(
           (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4.1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4.2",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
         ) ,"Service Disconnected",

      AND(
           (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5.1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5.2",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))>0)
         ) ,"Restored After Disconnect", 

      AND(
           (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
         ) ,"Resolved Before Letter", 
      AND(
           (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))>0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
          ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
         ) ,"Resolved After Letter", 
      ,"NA")

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Anonymous Your solution works with a slight modification but thanks for the solution. Right now the issue I am facing is how to display the measure. I need to populate the projects based on the status like Below

        Project Status                      Project Count

        Customer Notified              10

        Resolved Before Letter        5

        Letter Sent                           4

        But when I try to display the measure in a visual it just populates in the tooltips instead of values as I wanted. Is there a way to create another measure based on this measure? Please let me know if you have a solution.

         

        Thanks

        Sridhar

         

        P.S The modified version of your solution that worked for me is below

         

        SWITCH(TRUE(),
        AND(
             (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1.1",100)},[WB ID],[% Complete])))>0)
            ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1.2",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))=0)
           ) ,"Customer Notified",

        AND(
             (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
            ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.1",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.2",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.3",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.4",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3.5",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))=0)
           ) ,"Letter Sent",
        AND(
             (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
            ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4.1",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4.2",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
           ) ,"Service Disconnected",

        AND(
             (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
            ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
           &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
           &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5.1",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5.2",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))>0)
           ) ,"Restored After Disconnect", 

        AND(
             (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
            ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
           ) ,"Resolved Before Letter", 
        AND(
             (CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("1",100)},[WB ID],[% Complete])))>0)
            ,(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("2",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.1",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.2",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("5",100)},[WB ID],[% Complete])))>0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.3",100)},[WB ID],[% Complete])))>0)
          &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.4",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("3.5",100)},[WB ID],[% Complete])))=0)
            &&(CALCULATE(DISTINCTCOUNT([WB ID]),FILTER(<<TableName>>,CONTAINSROW({("4",100)},[WB ID],[% Complete])))=0)
           ) ,"Resolved After Letter", 
        ,"NA")