Targeting By Frequency

[RMSLUIS]

Hello,

I'm a new user in Power BI and I need help to targeting users in each target. Basically we have a transactions BD with respetive user, by date. We want to define a target for each user.

Thanks,

Rui Luís

The target rules are:

TARGET	Rule
Lover	≥ 4 transactions in 4 different weeks
Regular	2 or 3 transactions
Occasional	others

The transactions BD:

ID_TRANSACTION	USER	DATE	WEEK
1001	A	02-02-2019	5
1002	B	02-02-2019	5
1003	B	02-02-2019	5
1004	B	02-02-2019	5
1005	C	10-02-2019	6
1006	A	10-02-2019	6
1007	B	10-02-2019	6
1008	F	11-02-2019	7
1009	G	11-02-2019	7
1010	C	11-02-2019	7
1011	A	20-02-2019	8
1012	C	02-03-2019	9
1013	A	03-03-2019	9
1014	A	06-03-2019	10
1015	A	07-03-2019	10

The expected results are:

USER	TARGET
A	Lover
B	Regular
C	Regular
F	Occasional
G	Occasional

[Anonymous · Accepted solution]

@RMSLUIS ,

You could create the following Measure:

Target = 
var weeks = DISTINCTCOUNT(transactions[WEEK])
var tx = COUNT(transactions[ID_TRANSACTION])
return SWITCH(
    TRUE(),
    weeks >=4, "Lover",
    tx < 2, "Occasional",
    "Regular"
)

Cheers!

Nathan