Need a help with customer count who switch product.

[Anonymous]

Hello,

I am a new user learner of Dax.  Can you advise me on the following query?

For the following table, I need to find the total number of customers who switched from Financial to Non-Financial products and Non-Financial to Financial. If the customer bought both products on the same day then It is called a same-day purchase.  I want the results in one column.

I would really appreciate any given help. 

Many Thanks,

chana

Customer No.	Date	Product	Result
1	1/1/2000	Financial	Financial to Non-Financial
1	2/1/2000	Non-Financial	Financial to Non-Financial
2	3/1/2000	Non-Financial	Non-Financial to Financial
2	4/1/2000	Financial	Non-Financial to Financial
3	1/2/2000	Financial	Same-Day
3	1/2/2000	Non-Financial	Same-Day
4	1/2/2000	Non-Financial	Same-Day
4	1/2/2000	Financial	Same-Day
5	1/5/2000	Financial	Financial to Non Non-Financial
5	2/5/2000	Non-Financial	Financial to Non Non-Financial
5	3/5/2000	Financial	Financial to Non Non-Financial
5	4/5/2000	Non-Financial	Financial to Non Non-Financial

[Anonymous]

@Anonymous :

Thank you for the reply. It is not working on my end. Just to be clear Customer No is the Customer ID Number. 

[Anonymous]

Telling me "not working on my end" is no information at all. What's not working? Why is it not working? What do you get? Where's the problem? With respect to field names... well, I guess you're good enough to adjust my code to work with your model, right?

[Anonymous · Accepted solution]

[# Switch (F->NF)] =
// swap these to get the NF->F version
var FromState = "financial"
var ToState = "non-financial"
return
SUMX(
	DISTINCT( T[Customer No.] ),
	CALCULATE(
		var First2Rows =
			topn(2,
				T,
				T[Date],
				ASC
			)
		var First2RowsRanked =
			ADDCOLUMNS(
				First2Rows,
				"@Rank",
					RANKX(
						First2Rows,
						T[Date],, // 2 commas
						ASC,
						DENSE
					)
			)
		var IsFromFtoNF =
			not ISEMPTY(
				filter(
					First2RowsRanked,
					[@Rank] = 1
					&&
					T[Product] = FromState
				)
			)
			&&
			not ISEMPTY(
				filter(
					First2RowsRanked,
					[@Rank] = 2
					&&
					T[Product] = ToState
				)
			)
		return
			DIVIDE( IsFromFtoNF, IsFromFtoNF )
	)
)

Something like this... I guess. Be aware, though, that the measure is totally aware of all the filters in the current context.