Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Anonymous
Not applicable

Switch statement - Multiple expressions

‎02-01-2018 12:55 PM

I have a table with columns as mentioned below

Image 4.png

 

 

 

 

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

 

Message 1 of 8
11,786 Views
0
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable
In response to v-ljerr-msft

‎02-01-2018 08:57 PM

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

Message 3 of 8
12,371 Views
Anonymous
Not applicable
In response to Anonymous

‎02-02-2018 12:55 PM

@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

Message 4 of 8
12,362 Views
7 REPLIES 7
v-ljerr-msft
Microsoft Employee
Microsoft Employee

‎02-01-2018 07:14 PM

Hi @Anonymous,

 

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

=
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

Message 2 of 8
11,786 Views
0
Anonymous
Not applicable
In response to v-ljerr-msft

‎02-01-2018 08:57 PM

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

Message 3 of 8
12,372 Views
margerome13
New Member
In response to Anonymous

‎08-06-2019 02:32 AM

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.

 

CaptureUserFname
MR18Mark
AL18Alyanna
 RM107Brion
KV16Kisha
LB11Dryx
RV14Roms
JO107Jobelle
RM107Brion
MR107Mark
MA107Mar
LM107Lorena
AL107Alyanna
RM17Brion

 

Please help me. 

 

Thank you.

 

Message 8 of 8
9,444 Views
0
Anonymous
Not applicable
In response to Anonymous

‎02-02-2018 12:55 PM

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

Message 4 of 8
12,363 Views
Anonymous
Not applicable
In response to Anonymous

‎02-04-2018 07:17 PM

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

Message 5 of 8
11,758 Views
0
Anonymous
Not applicable
In response to Anonymous

‎02-05-2018 12:44 PM

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

Message 6 of 8
11,750 Views
0
Anonymous
Not applicable
In response to Anonymous

‎02-06-2018 09:09 PM

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

Capture.PNG

 

Message 7 of 8
11,726 Views

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All