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 Guys,
Could you please help me to create measure for the below requirement:
Customer | Shopping Date | Store |
Aiden | 1-Oct | Store_1 |
Aiden | 2-Oct | Store_2 |
Zachary | 3-Oct | Store_1 |
Zachary | 5-Oct | Store_2 |
Christian | 1-Oct | Store_1 |
Christian | 2-Oct | Store_2 |
Christian | 3-Oct | Store_3 |
Amey | 10-Oct | Store_1 |
I have one fact table, 3 dimensions ( Date, Customer and Store). and below is expected result.
Filter : 1st Oct to 5th Oct | |
Store | Distinct Customer Count |
Store_1 | 0 |
Store_2 | 2 |
Store_3 | 1 |
Basically I want to calculate Distinct Customer for Store . Customer visits different-2 stores over the time, requirement is to count Customer once where he/she has visited in last. For example, Aiden visited two different stores (Store 1 and Store 2 ) on 1st and 2nd October so here Customer should not be calculated for Store_1 as Customer has visited Store_2 also on next day means Customer should be calculated for the store where Customer has visited in last only.
Please note, solution should respect filter context as well. Like, in expected result Amey Customer is not calculated anywhere as it is not within the date range selected on report.
Thanks
Please note that the model is not correct. A good, professional model is always dimensional (star-schema). However, here is a formula in this bad model that should work. If not, then please adjust it accordingly.
[# Distinct Customers] =
// For each customer
// find the last store
// they shopped in. If
// in more than 1 store
// on the same day,
// assume the store
// that's last alphabetically.
var __visibleStores =
ALLSELECTED( T[Store] )
var __custsWithLastStore =
addcolumns(
distinct( T[Customer] ),
"@LastStore",
maxx(
topn(1,
CALCULATETABLE(
T,
__visibleStores
),
T[Shopping Date],
desc
),
T[Store]
)
)
var __count =
COUNTROWS(
filter(
__custsWithLastStore,
[@LastStore] in distinct( T[Store] )
)
)
return
__count
@Anonymous : Thank you for quick reply. My model is correct and in star scheme.
Could you please provide DAX again as per model below.
fact_shopping | |||
customer_id | date_id | store_id | amount |
5001 | 20201001 | 1 | 25 |
5001 | 20201002 | 2 | 40 |
5002 | 20201005 | 1 | 12 |
5002 | 20201002 | 2 | 96 |
5003 | 20201001 | 1 | 78 |
5003 | 20201002 | 2 | 8 |
5003 | 20201003 | 3 | 71 |
5004 | 20201010 | 1 | 69 |
dim_store | |
store_id | store_name |
1 | Store_1 |
2 | Store_2 |
3 | Store_3 |
dim_customer | |
customer_id | customer_name |
5001 | Aiden |
5002 | Zachary |
5003 | Christian |
5004 | Amey |
Please, next time when you post a question, do give people the data and model you really have and not some versions that then will require people to change their formulas. It's a waste of everybody's time. Thanks.
[# Distinct Customers] =
var __visibleStoresIds =
CALCULATETABLE(
distinct( dim_store[store_id] ),
ALLSELECTED( dim_store )
)
var __custsWithLastStore =
addcolumns(
distinct( dim_customer[customer_id] ),
"@LastStoreId",
// if there is more than 1 store
// on the particular date, the
// store with the highest store_id
// is selected
maxx(
topn(1,
CALCULATETABLE(
fact_shopping,
__visibleStoresIds,
all( dim_store )
),
fact_shopping[date_id],
desc
),
fact_shopping[store_id]
)
)
var __count =
COUNTROWS(
filter(
__custsWithLastStore,
[@LastStoreId] in distinct( dim_store[store_id] )
)
)
return
__count
@Anonymous , You can create a rank column and count only rank 1
rankx(filter(table,[Customer] =earlier([Customer])),[Shopping],,asc,dense)
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |