Forum Discussion
trying to add an expression to a switch statement
I created a switch statement based on the suggestion in the below thread.
http://community.powerbi.com/t5/Desktop/Switch-statement-Multiple-expressions/m-p/350144
right now I do have another issue for populating the status as I need to incorporate a 'Service Disconnected Greater Than 90 days' one too. I wrote the following expression but it doesnt seem to work. Let me know if you have any solution for this. Thanks in advance
the code I added to the existing code is below
AND(
(DATEDIFF(Tasks[ServiceDisconnectedPartService],UTCTODAY(),DAY))>90
&&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.4.1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
),"7 - Service Disconnected > 90 Days"
"NA")
But I only get this(Project status(Original Code), PS1(my code added), Image1(Table as needed0 Image 2(output of my code)
Original Code
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")
Hi Anonymous,
Based on my test, if adding the new logic above "Service Disconnected" instead of adding it at the bottom, then it will work as expected. :smileyhappy:
ProjectStatus2 = 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( (DATEDIFF(Tasks[ServiceDisconnectedPartService],UTCTODAY(),DAY))>90 ,(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.4.1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0) ),"7 - Service Disconnected > 90 Days", 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")Regards
3 Replies
- v-ljerr-msft
Microsoft Employee
Hi Anonymous,
Based on my test, if adding the new logic above "Service Disconnected" instead of adding it at the bottom, then it will work as expected. :smileyhappy:
ProjectStatus2 = 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( (DATEDIFF(Tasks[ServiceDisconnectedPartService],UTCTODAY(),DAY))>90 ,(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3.4.1",100)},[WBID],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0) ),"7 - Service Disconnected > 90 Days", 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")Regards
- v-ljerr-msft
Microsoft Employee
Hi Anonymous,
Could you share a dummy pbix file which can reproduce the issue(also with the expected result), so that we can better assist on this issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:
Regards
- AnonymousNot applicable
v-ljerr-msft I created a dummy .pbix file and uploaded it to the following onedrive location
https://1drv.ms/f/s!AkUUe-6V4o1Ug0icJrfj6OuJg
Please let me know if you any solution for this issue. Any help is appreciated. Thanks in advance