cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AsadChanna14
Helper I
Helper I

Need a help with customer count who switch product.

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 ProductResult
11/1/2000Financial Financial to Non-Financial
12/1/2000Non-Financial Financial to Non-Financial
23/1/2000Non-FinancialNon-Financial to Financial
24/1/2000FinancialNon-Financial to Financial
31/2/2000FinancialSame-Day
31/2/2000Non-FinancialSame-Day
41/2/2000Non-FinancialSame-Day
41/2/2000FinancialSame-Day
51/5/2000FinancialFinancial to Non Non-Financial
52/5/2000Non-FinancialFinancial to Non Non-Financial
53/5/2000FinancialFinancial to Non Non-Financial
54/5/2000Non-FinancialFinancial to Non Non-Financial
1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

[# 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.

View solution in original post

3 REPLIES 3
AsadChanna14
Helper I
Helper I

@daxer :

 

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

 

 

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?

daxer
Solution Sage
Solution Sage

[# 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.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.