Trying to get a single Status along with the Count

[Anonymous]

I am trying to get a single status for my cases(Projects) where based on different conditions, the cases are in different stages. I wrote a code using a switch experssion but I am not getting the desired result. 

Code:

Milestones_v1 = SWITCH(TRUE(),
AND(
     (CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("1",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>0)
    ,(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("2",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("4",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
   ) ,"Planning",
AND(
     (CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("1",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>=75)
    ,(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("2",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("4",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
   ) ,"Development",
AND(
     (CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("1",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>=75)
    ,(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("2",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>=75)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("4",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
   ) ,"Filing",
AND(
     (CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("1",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>=75)
    ,(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("2",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>=75)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>=75)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("4",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))>=0)
   ) ,"Implementation",

 AND(
     (CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("1",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    ,(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("2",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("3",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
    &&(CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,AND(CONTAINSROW({("4",100)},[TaskWBS],[TaskPercentCompleted]),Tasks[ProjectId]=EARLIER(Tasks[ProjectId]))))=0)
   ) ,"Case Created",

"Case Needs Review")

And the sample template looks like below

I tried using the tips from the article below but I couldn't make it work

https://community.powerbi.com/t5/Desktop/Switch-statement-Multiple-expressions/m-p/350144

Any Help is appreciated. 

Thanks

[v-cherch-msft]

Hi @Anonymous

It seems the conditions in the AND Function are false.

Regards,

Cherie

[Seward12533]

What errors or incorrect results are you getting? Can you post a sample PBIX file that we can try to help troubleshoot? 

[Anonymous]

@Seward12533 I uploaded the sample .pbix file and the data file to One drive. You can access the files through the link below.

https://1drv.ms/f/s!AkUUe-6V4o1UhSdeleZUqmE60E-q

The error I am getting is that even if one of the conditions is met I am getting the same status not the status that is being met.

For example, one of the status has to be "Filing' as the conditions are being met but it just shows as 'Case Created'. 

Thanks in Advance

[Seward12533 · Accepted solution]

@Anonymous Hi bunch of things going on.  Looks like you mixing things up. COUNTROWS will return number of FILTERERED ROWS which is your data is 1 or 0j and never approaches 75.  Working backwards inside the FILTER for the first term you use

• EARLIER is a bit tricky but in this context (when you leave off the 2nd term implying 1) is basically VALUE for this ROW in the table. So it filters Table to where ProjectID = the Project ID of this row.
• The CONTAINSROW will only return ROWs where WBS = "1" and PercentTaskComplete = 100% (none of your data meets thsi condition)

Few tips:

• use VARiables to make your formulas easier to both read and write.
• If the filter context returns no rows or if an equality test is FALSE, PowerBI returns NULL or BLANK this is not always the same as zero and PBI won't display a value in a visual if all the values are BLANK.

IF you want to test to see if project is > 75% complete something like this would be more appropriate

CALCULATE(DISTINCTCOUNT([ProjectId]),FILTER(Tasks,[TaskWBS]="1"&&[TaskPercentCompleted]>=75&&[ProjectId]=EARLIER(Tasks[ProjectId])))+0

It will be = "1"  IF that ProjectID has Perctent Complete >= 75 and WBS="1" and "0" for everything else for any row with a task in that project.  SEE [Milestone V3] in my sample.

Looking at the your logic I think you wanted to test something more like IF Percetnt Complete of the key WBS tasks in your project were =0, >0 and <75%, or  > 75%.  To do this I'd recommend buildilng a table using Summarize and then using that for your testing.  

Main WBS Summary = SUMMARIZE(CALCULATETABLE(Tasks,Tasks[TaskWBS] in {"1","2","3","4"}),Tasks[ProjectName],Tasks[ProjectId],"WBS1",CALCULATE(AVERAGE(Tasks[TaskPercentCompleted]),Tasks[TaskWBS]="1"),"WBS2",CALCULATE(AVERAGE(Tasks[TaskPercentCompleted]),Tasks[TaskWBS]="2"),"WBS3",CALCULATE(AVERAGE(Tasks[TaskPercentCompleted]),Tasks[TaskWBS]="3"),"WBS4",CALCULATE(AVERAGE(Tasks[TaskPercentCompleted]),Tasks[TaskWBS]="4"))

Then a MEASURE to test your conditions after linking it  to your Model

Milestone V4 = 
VAR WBS1 = CALCULATE(MAX('Main WBS Summary'[WBS1]),Projects)
VAR WBS2 = CALCULATE(MAX('Main WBS Summary'[WBS2]),Projects)
VAR WBS3 = CALCULATE(MAX('Main WBS Summary'[WBS3]),Projects)
VAR WBS4 = CALCULATE(MAX('Main WBS Summary'[WBS4]),Projects)
RETURN
SWITCH(TRUE(),
    ISBLANK(WBS1)&&ISBLANK(WBS2)&&ISBLANK(WBS3)&&ISBLANK(WBS4),BLANK(),
    WBS1=0&&WBS2=0&&WBS3=0&&WBS4=0,"Case Created",
    WBS1>=75&&WBS2>=75&&WBS3>=75&&WBS4>=0,"Implementation", 
    WBS1>=75&&WBS2>=75&&WBS3>=0,"Filing",
    WBS1>=75&&WBS2>=0,"Development",
    WBS1>0,"Planning",
  "Case Needs Review")

as a bonus I added a calculated column for WBS level that basically calculates the number of periods.  I then use this to filter the tasks displayed to specific WBS levels and have a slicer to control that.

[Anonymous]

@Seward12533 Thanks for the solution. It worked perfectly and I was able to incorporate this into my model. However I am trying to get a count of the case status from the measure and can only populate into the tooltips and not into the values field so that I can count the status. Is there a way to add this measure as a column and try to get a count? I was trying but I was getting a "circular Dependency" error. Any help is appreciated. Thanks

[Seward12533]

If you want to count the status then use a calculated column in the Calcualted Table vs measure and then write a measure to count the number rows.  For the calculated column you dont' need to calculate and can just reference the column name.

Milestone Count = COUNTROWS('Main WBS Summary')

Milestone Column = 
VAR WBS1 = [WBS1]
VAR WBS2 = [WBS2]
VAR WBS3 = [WBS3]
VAR WBS4 = [WBS4]
RETURN
SWITCH(TRUE(),
    ISBLANK(WBS1)&&ISBLANK(WBS2)&&ISBLANK(WBS3)&&ISBLANK(WBS4),BLANK(),
    WBS1=0&&WBS2=0&&WBS3=0&&WBS4=0,"Case Created",
    WBS1>=75&&WBS2>=75&&WBS3>=75&&WBS4>=0,"Implementation", 
    WBS1>=75&&WBS2>=75&&WBS3>=0,"Filing",
    WBS1>=75&&WBS2>=0,"Development",
    WBS1>0,"Planning",
  "Case Needs Review")

[Anonymous]

Thanks. That works