looking for a calculated column

[sanjay009]

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

fruit  fail

veg  pass

bean pass

table 2:

ID Type name status

2  fruit    apple         pass

3  veg     eggplant pass

4  beans soya     pass

5 fruit grapes  fail

6 veg tomato pass

7 fruit  banana pass

thank you

[ryan_mayu · Accepted solution]

@sanjay009 

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 · Accepted solution]

@sanjay009 

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

 

[sanjay009]

ran into issue. if there are no records. its displaying failed .can we have None instead failed for no records Type.

[ryan_mayu · Accepted solution]

@sanjay009 

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.

[sanjay009]

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