The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
hi @Anonymous
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:
hi @Anonymous
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:
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.
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 🙂