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

v-ljerr-msft

Microsoft Employee

8 years ago

Hi Anonymous,

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

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

Regards

Anonymous

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({("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
  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
margerome13
New Member
7 years ago
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.

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

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