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