Distinct count of Customer Name for different dates and considering filters

[Gutierrez]

Hello, currently I need to get the number of unique customer sales per agent and I'm using the distinct count of customer name column considering all the filters (see below) 

 CALCULATE (

        DISTINCTCOUNT ( SalesDim[Customer Name] ) ,

        SalesDim[Type of Transaction] = "sale",

        SalesDim[Has Relocation Payments To This Order] = 0,

        SalesDim[POS Campaign] = BLANK (),

        SalesDim[Product ID] <> 44405,

        SalesDim[Product ID] <> 44406,

        SalesDim[Product ID] <> 44404

    )

The problem is that if a customer bought something yesterday and then today, if I filter the dates for these dates it will show up only 1 (because it's considering the same customer name) so I need to change this so it counts the distinct customer names for every day. Look an example table below

Product ID	Type of Transaction	Employee Name	POS Campaign	Has Relocation Payments To This Order	Date Only	Customer Name
52988	sale	Agent A		0	12/15/2022	Customer A
1913547	sale	Agent A		0	12/15/2022	Customer B
2699468	sale	Agent A		0	12/14/2022	Customer C
23566	sale	Agent A		0	12/14/2022	Customer B
51570	sale	Agent A		0	12/14/2022	Customer B

If I filter 12/14 and 12/15 for dates, the number of Unique Customer Sales should be 4 instead of 3 (Customer B appears twice on 12/14 so here it should be 1, and appears one more time on 12/15 so for this particular customer should be 2 sales between that date range). Of course, I still need to consider the filters showing above.

Thank you in advance.

[v-cgao-msft · Accepted solution]

Hi @Gutierrez ,

Please try:

Measure = 
SUMX(
    SUMMARIZE(
        SalesDim,
        SalesDim[Customer Name],
        SalesDim[Date Only],
        "distcount",
        CALCULATE (
            DISTINCTCOUNT(SalesDim[Customer Name]),
            SalesDim[Type of Transaction] = "sale"
                && SalesDim[Has Relocation Payments To This Order] = 0
                && SalesDim[POS Campaign] = BLANK ()
                && SalesDim[Product ID] <> 44405
                && SalesDim[Product ID] <> 44406
                && SalesDim[Product ID] <> 44404
        )
    ),
    [distcount]
) 

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Gutierrez]

@v-cgao-msft  Thank you! that worked for me! Appreciate it!