cancel
Showing results for
Did you mean:
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 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
1 ACCEPTED SOLUTION
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 =
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.

3 REPLIES 3
Helper I

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

Solution Sage

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?

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 =
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.

Announcements

#### Manage your user group events

Check out the News & Announcements to learn more.

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

#### Microsoft named a Leader in The Forrester Wave

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

Top Solution Authors
Top Kudoed Authors