IF(COUNTIF)

[awsiya]

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

[awsiya]

It worked. Thank you so very much. 

[awsiya]

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 🙂

[Ritaf1983]

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

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

)

Link to the sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly