DAX Measure if customer bought some product

[EvertonRamone]

I need a DAX measure wich shows me a flag (1 or 0) if a customer bought the product "A" someday (for the first time) and later have bought product "C"

Measure1: Flag (1 or 0) in the customer name side if bought

Measure2: When was the first time that he bought product C

If need, here are the tables:

KeyDate	KeyCustomer	KeyProduct	Total
1	1	1	12,9
1	2	2	13
1	3	1	156,4
1	4	1	564,8
2	1	1	894,8
2	2	1	56,5
3	1	3	564,85
3	2	3	564,8
4	1	2	1325,6
4	2	2	132,3

KeyProduct	Product
1	A
2	B
3	C

KeyCustomer	Name
1	Jean
2	Mari
3	Lisa
4	Julian

Calendar
KeyDate	Date
1	01/01/2018
2	02/01/2018
3	03/01/2018
4	04/01/2018

Can someone help me?

Att..

[v-danhe-msft]

Hi @EvertonRamone,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @EvertonRamone,

Based on my test, you could refer to below formula:

Measure1Flag = IF(CALCULATE(DISTINCTCOUNT(FactSales[KeyProduct]),FILTER(ALL(FactSales),(FactSales[KeyProduct]=1 || FactSales[KeyProduct]=3 )&&FactSales[KeyCustomer]=MAX(FactSales[KeyCustomer])))=2,
         CALCULATE(DISTINCTCOUNT(Customer[KeyCustomer]),FILTER(FactSales,(FactSales[KeyProduct]=1 ||FactSales[KeyProduct] = 3)&& FactSales[KeyCustomer] = MAX(FactSales[KeyCustomer]))),
         0)

FirstTimeBoughtProductC = CALCULATE(MAX('Calender'[Date]),FILTER(ALL('Calender'),'FactSales'[Measure1Flag]<>0))

Result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/vuzdj6shen674dn/ZZZZZ.pbix?dl=0

Regards,

Daniel He

[Anonymous]

@EvertonRamone not sure if I exactly get your question.

Do you want value 1 if a customer bought product A before product C

OR

Do you want value 1 if a customer bought product A and product C

Here are some options you can mix and match to get both the above

For the first date when a customer bought A or C you can use the formula 

FirstDateBoughtC = CALCULATE(MIN(Date[Date]), ALLEXCEPT(FactSales, FactSales[KeyCustomer]), FILTER(Product, Product[Product] = "C"))

FirstDateBoughtA = CALCULATE(MIN(Date[Date]), ALLEXCEPT(FactSales, FactSales[KeyCustomer]), FILTER(Product, Product[Product] = "A"))

Measure = SWITCH(TRUE(),

                                FirstDateBoughtA < FirstDateBoughtC && FirstDateBoughtA <> NULL && FirstDateBoughtC <> NULL,1

                                ......some condition, 1/0,

                                0)

[EvertonRamone]

@Anonymous, I want value 1 if customer bought product "A "someday (before buying product C) and then later bought product C

[Anonymous]

@EvertonRamone try the formulae in my above comment and that should do the trick.

[EvertonRamone]

@Anonymous, It didn't work... My fact table and date table have the relashionship set to filter only one direction, not both... I think that this is the reason why isn't working. Is there any other way?

[Anonymous]

@EvertonRamone it should work, can you plz post your power bi file here so i can check whats going on here.

also if u cant share the data feel free to create one with dummy data.

to post file post it to dropbox or google drive and paste the link here.