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

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

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.

     

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    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")

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • 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.