Forum Discussion

awsiya's avatar
awsiya
Helper I
2 years ago
Solved

IF(COUNTIF)

Hi PowerBI Community,

I am facing a challenge with a DAX formula. I currently have an Excel formula, which I have included screenshot below. The formula is an IF(CountIF) formula. Essentially, I have a column in Excel where some values are repeated, and I intend to display unique values as 1, but when there is a duplicate value, I want to display 0. You can see an example below.

Now, I have the same column in Power BI, and I'd like to use DAX (preferably a calculated column) to achieve the same result. I have made an attempt using differnet approached but failed..

Is there a way to achieve this in Power BI? if yes could you please guide. I have same column names in powerBI

 



Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi awsiya 
    Add index column via PQ

    and modify the formula to :

    FirstAppearance =
    VAR CurrentValue = 'table'[value]
    RETURN
    IF (
        CALCULATE (
            COUNTROWS ('table'),
            FILTER (
                'table',
                'table'[value] = CurrentValue
                    && 'table'[Index] <= EARLIER('table'[Index])
            )
        ) = 1,
        1,
        0
    )
    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

4 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi awsiya 

    You can add a calculated column with the formula :

    IsUnique = COUNTROWS(FILTER('Table', 'Table'[value] = EARLIER('Table'[value]))) = 1
     
    **It returns true/false, so if you want 0/1 just change the datatype to "whole number"
    Unfortunately, i am out of the limit of images I can add to discussions, so if you need to see the image please download it from : Here 
    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
    • Ritaf1983's avatar
      Ritaf1983
      Super User
      2 years ago

      Hi awsiya 
      Add index column via PQ

      and modify the formula to :

      FirstAppearance =
      VAR CurrentValue = 'table'[value]
      RETURN
      IF (
          CALCULATE (
              COUNTROWS ('table'),
              FILTER (
                  'table',
                  'table'[value] = CurrentValue
                      && 'table'[Index] <= EARLIER('table'[Index])
              )
          ) = 1,
          1,
          0
      )
      If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
  • awsiya's avatar
    awsiya
    Helper I
    2 years ago

    Hi Ritaf1983 . Thank you for giving solution. But it should show True to at least one of the duplicates. e., if I have two or three (let us assume 3) values  that are identical, it shoudl reflect at least 1 true and mark the rest of the duplicates false. If you look at my orginal excel sheet that is exactly what excel formula had done. If there is set of 2 duplicataes, it will mark one true (1) and one false (0). with the formula that you provided, it surely is giving me true for the individual values but also giving me false to all other duplicates. Please see my screenshot of excel i have explained in little more detail what I wanted. I would really apprciate if solution is provided 🙂