Help to Calculate Distinct Count for each Store

[Anonymous]

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

[Anonymous]

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]

@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

[Anonymous]

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

[amitchandak]

@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