Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
@Anonymous :
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?
[# 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
61 | |
50 | |
45 | |
20 | |
17 |