cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Customer who buys every day or every time period

I have a large dataset, but I have prepared a demo for this question.

I want to find out the unique count of customers who have bought from us in each day or each time period such as each month.

I want to do this as a MEASURE not a calculated column

So in the table below we can see customer Bruce has purchased each of the 3 days, customer Tim has purchased each of the 3 days, and other 2 customers have purchased on 2 out of the 3 days. But if I used product "Battery" as a slicer, then it is only Tim who has bought "Battery" in all 3 days.

CustIDCustNameProductPurchase Date

 AX00001 Bruce Battery 1-Aug AX00002 Alfred Node 1-Aug AX00003 Tim Battery 1-Aug AX00001 Bruce Node 2-Aug AX00002 Alfred Node 2-Aug AX00003 Tim Battery 2-Aug AX00004 Damian Node 2-Aug AX00001 Bruce Battery 3-Aug AX00003 Tim Battery 3-Aug AX00004 Damian Node 3-Aug
2 ACCEPTED SOLUTIONS
Super User

Try this:

1. Place Product in the rows of a table visual

2. Create this measure:

```Measure =
VAR AuxT_ =
DISTINCT ( Table1[CustID] ),
"NumDays", CALCULATE ( COUNT ( Table1[PurchaseDate] ) )
)
VAR DaysInPeriod_ =
COUNTROWS ( ' Date' ) // We assume here you have a calendar table and are using it in a slicer to select the period
RETURN
COUNTROWS ( FILTER ( AuxT_, [NumDays] = DaysInPeriod_ ) )
```

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers

Helper III

Actually I figured it out myself. But many thanks to your syntax - I tweaked that a bit and made it work on my scenario. Many thanks. My solution if is of interest is: -
=VAR MiniTable =
ADDCOLUMNS ( DISTINCT (Client[ID]), "NumDays", CALCULATE ( [Days in Period], FILTER( VALUES( Sales[Value]), Sales[Value] >= 1 ) ) )
VAR DaysInPeriod = [Days in Period]
RETURN
COUNTROWS ( FILTER ( MiniTable, [NumDays] = DaysInPeriod) )

2 REPLIES 2
Super User

Try this:

1. Place Product in the rows of a table visual

2. Create this measure:

```Measure =
VAR AuxT_ =
DISTINCT ( Table1[CustID] ),
"NumDays", CALCULATE ( COUNT ( Table1[PurchaseDate] ) )
)
VAR DaysInPeriod_ =
COUNTROWS ( ' Date' ) // We assume here you have a calendar table and are using it in a slicer to select the period
RETURN
COUNTROWS ( FILTER ( AuxT_, [NumDays] = DaysInPeriod_ ) )
```

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers

Helper III

Actually I figured it out myself. But many thanks to your syntax - I tweaked that a bit and made it work on my scenario. Many thanks. My solution if is of interest is: -
=VAR MiniTable =
ADDCOLUMNS ( DISTINCT (Client[ID]), "NumDays", CALCULATE ( [Days in Period], FILTER( VALUES( Sales[Value]), Sales[Value] >= 1 ) ) )
VAR DaysInPeriod = [Days in Period]
RETURN
COUNTROWS ( FILTER ( MiniTable, [NumDays] = DaysInPeriod) )

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.