Determine if the value occurs more than once in the column

[Alexis-Edin]

Hi everyone, 

I would like to create a "loyalty rate"measure's in my database. 

My database bring together all clients of my compagny per year, so one line correspond to one client with all these purchases. 

The client has an ID, if this ID found twice or more in the column, it means that the customer is not a new one, but a old one who made some purchases last year. 

I would like to know if it's possible to create a "conditional column" where I can find the information, new customer, loyal customer, old customer.. With this information, I could make some measures which will be powerfull for my PowerBI 

If you need an example of my database, tell me, I will send you an image. 

Thank you in advance

[FreemanZ · Accepted solution]

hi @Alexis-Edin 

supposing your data looks like:

try to add three columns like:

IsNewClient =
VAR _currentyear = YEAR(TODAY())
VAR _table =
FILTER(
        TableName,
        TableName[ClientID] = EARLIER(TableName[ClientID])
)
VAR _condition1 =
COUNTROWS(
    FILTER(
       _table,
        TableName[Year] = _currentyear
    )
)
VAR _condition2 =
COUNTROWS(
    FILTER(
       _table,
        TableName[Year] < _currentyear
    )
)
RETURN
IF(
    _condition1>0&&_condition2=0,
    "Yes", "No"
)

IsLoyalClient =
VAR _currentyear = YEAR(TODAY())
VAR _table =
FILTER(
        TableName,
        TableName[ClientID] = EARLIER(TableName[ClientID])
)
VAR _condition1 =
COUNTROWS(
    FILTER(
       _table,
        TableName[Year] = _currentyear
    )
)
VAR _condition2 =
COUNTROWS(
    FILTER(
       _table,
        TableName[Year] < _currentyear
    )
)
RETURN
IF(
    _condition1>0&&_condition2>0,
    "Yes", "No"
)

IsOldClient =
VAR _currentyear = YEAR(TODAY())
VAR _table =
FILTER(
        TableName,
        TableName[ClientID] = EARLIER(TableName[ClientID])
)
VAR _condition1 =
COUNTROWS(
    FILTER(
       _table,
        TableName[Year] = _currentyear
    )
)
VAR _condition2 =
COUNTROWS(
    FILTER(
       _table,
        TableName[Year] < _currentyear
    )
)
RETURN
IF(
    _condition1=0&&_condition2>0,
    "Yes", "No"
)

it worked like:

[jaweher899]

Yes, it is possible to create a "conditional column" in your database to determine if a customer is a new, loyal, or old customer based on the number of times their ID appears in the column. To do this, you can use the "IF" function in Power Query.

Here is an example of how the formula for the "conditional column" could look like:

IF [ID] = 1 THEN "New Customer" ELSEIF COUNTIF([ID],[ID]) > 1 THEN "Loyal Customer" ELSE "Old Customer"

You can use this formula to create a new column in your database, and use this information to create your "loyalty rate" measure.

[Alexis-Edin]

It doesn't work.. I have 40 000 differnts ID. I don't want to know for one ID. May be I didn't write correctly your formula but "countif" doesn't exist in PowerQuery. 

If you have an other solution, I will take it 🙂