Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

looking for a calculated column

i want to add a new column(status) to my table as below   The status column should be pass if type is all pass and fail if any fail by type.   table 1: (expected Status column) Type  status fru...
Need Help
Show and Tell
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    Anonymous 

     

    please try below DAX

    status = 
VAR failnum = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[type]=Table1[type]&&'Table'[status]="fail"))
return if(failnum>0,"Fail","pass")

     

  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    Anonymous 

    I think it always display pass because I only calculate fail times.

     

    You can try below measure

    status = 
VAR failnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[status]="fail"))
VAR passnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[status]="pass"))
return if(failnum=0&&passnum=0,blank(),if(failnum>0,"Fail","pass"))

    bean also shows blank because its name in your anothe table is beans and I also add a test type for testing.

    Hope this is helpful.

     

ryan_mayu's avatar
ryan_mayu
Super User
6 years ago

Anonymous 

I think it always display pass because I only calculate fail times.

 

You can try below measure

status = 
VAR failnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[status]="fail"))
VAR passnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[status]="pass"))
return if(failnum=0&&passnum=0,blank(),if(failnum>0,"Fail","pass"))

bean also shows blank because its name in your anothe table is beans and I also add a test type for testing.

Hope this is helpful.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

i have another scenario that if value >90 display 'pass' and if value >80 dispaly 'average' and value less than 80 red' for type fruit from above table.

 

i tried below but didn't work

 

status2 =
VAR failnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[value]>90)
VAR avgnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[value] >80 && 'TEST'[value] <89 )
VAR passnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[value]<80)

return if(failnum=0&&passnum=0,"Average",if(failnum>0,"Fail","pass"))