Forum Discussion

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

General Comment

Need Help

Tips and Tricks

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

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({("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

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({("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
Not applicable
8 years ago
Do you have a seperate field for the Project ID? Could you share the data model and sample data
- Anonymous
  Not applicable
  8 years ago
  Anonymous Yes I do have a seperate field for ProjectID in the same table
  - Anonymous
    Not applicable
    8 years ago
    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

Forum Discussion

Switch statement - Multiple expressions

Related content

Multiple IF statements in DAX

Multiple nested IF statements

DAX expression ignoring blanks statement when extra filter is added

single dataset refresh, we observe multiple SQL statements

Multiple IF statements