Forum Discussion

NISH72's avatar
NISH72
Helper IV
6 years ago
Solved

Conditional calculated column

Hi all I have the following table

Id.     Name

1.       A

1.        B

2.        C

3.        D

4.        E

 

If a particular Id has multiple names attached to it.( For eg id 1 has two names attached to it.). Then i want to show it as 1 in new column. If any id  does not have multiple names attached to it then it would show as 0 on new column. I think I have to use if statement but I don't know which formula to use. Thank you

Need Help
Show and Tell
  • camargos88's avatar
    camargos88
    6 years ago

    Hi NISH72 ,

     

    Try this code for a new column:

    NewColumn =
    VAR _result = CALCULATE(COUNT('Table'[Id]); ALLEXCEPT('Table'; 'Table'[Id]))
    RETURN IF(_result > 1; 1; 0)
     

6 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi NISH72 ,

     

    Try this code for a new column:

    NewColumn =
    VAR _result = CALCULATE(COUNT('Table'[Id]); ALLEXCEPT('Table'; 'Table'[Id]))
    RETURN IF(_result > 1; 1; 0)
     
    • NISH72's avatar
      NISH72
      Helper IV
      6 years ago

      When I add this formula in the new column it's throwing syntax error. It's not accepting return if function. Thank you

      • camargos88's avatar
        camargos88
        Community Champion
        6 years ago

        NISH72 ,

         

        Try with comma:

         

        NewColumn =
        VAR _result = CALCULATE(COUNT('Table'[Id]), ALLEXCEPT('Table', 'Table'[Id]))
        RETURN IF(_result > 1, 1, 0)
    • mahoneypat's avatar
      mahoneypat
      Microsoft Employee
      6 years ago

      Please try this expression for your column

       

      Duplicates = var currentid = 'ID'[Id]
      var countthisid = COUNTROWS(Filter('ID', 'ID'[Id]=currentid))
      return if(countthisid>1,1,0)
       

      If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

      Regards,

      Pat