Solved: Switch statement - Multiple expressions

Anonymous · ‎02-01-2018

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")

Anonymous · ‎02-01-2018

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")

View solution in original post

Anonymous · ‎02-02-2018

@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")

View solution in original post

v-ljerr-msft · ‎02-01-2018

Hi @Anonymous,

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

=
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 · ‎02-01-2018

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")

margerome13 · ‎08-06-2019

Hi,

I can't figure how to use the Switch statement in Power BI. I have this column with all user ID, they appear several times in the row.

My goal is create a Fname Column using switch, that for every Capture User ID below, the Fname equivalent will appear.

CaptureUser	Fname
MR18	Mark
AL18	Alyanna
RM107	Brion
KV16	Kisha
LB11	Dryx
RV14	Roms
JO107	Jobelle
RM107	Brion
MR107	Mark
MA107	Mar
LM107	Lorena
AL107	Alyanna
RM17	Brion

Please help me.

Thank you.

Anonymous · ‎02-02-2018

@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")

Anonymous · ‎02-04-2018

Do you have a seperate field for the Project ID? Could you share the data model and sample data

Anonymous · ‎02-05-2018

@Anonymous Yes I do have a seperate field for ProjectID in the same table

Anonymous · ‎02-06-2018

Create a Column (ProjectStatus) in the Tasks table and use the below formula

ProjectStatus = SWITCH(TRUE(),
AND(
   (CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("1.1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    ,(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("1.2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("4",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("5",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    ),"Customer Notified",
AND(
     (CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    ,(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.3.1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.3.2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.3.3",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.3.4",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.3.5",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.4",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.5",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("4",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("5",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
   ) ,"Letter Sent",
AND(
     (CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    ,(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.3",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.4.1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.4.2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.5",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("4",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
   ) ,"Service Disconnected",
AND(
     (CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    ,(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
   &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
   &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.3",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.4",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.5.1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.5.2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("4",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("5",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
   ) ,"Restored After Disconnect",
AND(
     (CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    ,(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("5",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.3",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.4",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.5",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("4",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
   ) ,"Resolved Before Letter",
AND(
     (CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    ,(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.2",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("5",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.3",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
&&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.4",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.5",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("4",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
   ) ,"Resolved After Letter",
"NA")

After that when you pull out the Project status and distinct count of ProjectId you will be see the required output

Switch statement - Multiple expressions

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly