Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

New Customers who attended events - DAX

Hi Everyone,

 

I need help in determing the same old question but the data structure is not letting my head think straight - finding new customers.

I got two tables with folliwng fields

-Events

  • EventID - has unique values
  • EventDate - the date when an event was held

-Customers

  • EventID - got non unique values- as multiple people would be attending an event
  • CustomerID - non unique values - as a customer might be attending multiple events

-DateTable

I want to see the numbef of new customers who have attended the events say in last month or last year.

 

I have to identify the customers for any event from customer table and all details of events in events table

So the dates and events will be coming from events table and customers who attended those events from customer table- 

In order to get new customers say in 2018, I would need to find all events for 2018 and then all customers who attended those events( from customer table) but then need to see who have not attended anything before 2018.

 

Any help would be greatly appriciated.

Thanks

emudria

 

 

 

 

2 ACCEPTED SOLUTIONS
v-jiascu-msft
Employee
Employee

Hi Emudria,

 

Please download the demo in the attachment. The [Measure] shows the details while the [Result] shows the expected result.

Result =
VAR beforeCurrentCustomers =
    CALCULATETABLE (
        VALUES ( Customers[CustomerID] ),
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] <= EOMONTH ( MIN ( 'Calendar'[Date] ), -1 )
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Customers[CustomerID] ),
        FILTER ( 'Customers', NOT 'Customers'[CustomerID] IN beforeCurrentCustomers )
    )

New-Customers-who-attended-events-DAX

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

It's my pleasure. 

Since I don't have the real data, the order of the events could be complicated. Anyway, you can sort them by the orders you like then add an index to fix it. The logic is similar. Please refer to the formulas below and the snapshot.

Measure 2 =
VAR beforeCurrentCustomers =
    CALCULATETABLE (
        VALUES ( Customers[CustomerID] ),
        FILTER ( ALL ( Events ), Events[Index] < MIN ( Events[Index] ) )
    )
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Customers[CustomerID] ), [CustomerID], "-" ),
        FILTER ( 'Customers', NOT 'Customers'[CustomerID] IN beforeCurrentCustomers )
    )
Result 2 =
VAR beforeCurrentCustomers =
    CALCULATETABLE (
        VALUES ( Customers[CustomerID] ),
        FILTER ( ALL ( Events ), Events[Index] < MIN ( Events[Index] ) )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Customers[CustomerID] ),
        FILTER ( 'Customers', NOT 'Customers'[CustomerID] IN beforeCurrentCustomers )
    )

New-Customers-who-attended-events-DAX2

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-jiascu-msft
Employee
Employee

Hi Emudria,

 

Please download the demo in the attachment. The [Measure] shows the details while the [Result] shows the expected result.

Result =
VAR beforeCurrentCustomers =
    CALCULATETABLE (
        VALUES ( Customers[CustomerID] ),
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] <= EOMONTH ( MIN ( 'Calendar'[Date] ), -1 )
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Customers[CustomerID] ),
        FILTER ( 'Customers', NOT 'Customers'[CustomerID] IN beforeCurrentCustomers )
    )

New-Customers-who-attended-events-DAX

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-jiascu-msft OMG! have no words to thank you. It works exactly the way it was needed - beautiful logic applied.

 

Is it possible to modify this so that if I put a filter on an event, it gives me new memebers (count & ID ) for that event. Currently it works with date filter but you know on an event filter it will give all customers as new customers.

 

Thanks

 

It's my pleasure. 

Since I don't have the real data, the order of the events could be complicated. Anyway, you can sort them by the orders you like then add an index to fix it. The logic is similar. Please refer to the formulas below and the snapshot.

Measure 2 =
VAR beforeCurrentCustomers =
    CALCULATETABLE (
        VALUES ( Customers[CustomerID] ),
        FILTER ( ALL ( Events ), Events[Index] < MIN ( Events[Index] ) )
    )
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Customers[CustomerID] ), [CustomerID], "-" ),
        FILTER ( 'Customers', NOT 'Customers'[CustomerID] IN beforeCurrentCustomers )
    )
Result 2 =
VAR beforeCurrentCustomers =
    CALCULATETABLE (
        VALUES ( Customers[CustomerID] ),
        FILTER ( ALL ( Events ), Events[Index] < MIN ( Events[Index] ) )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Customers[CustomerID] ),
        FILTER ( 'Customers', NOT 'Customers'[CustomerID] IN beforeCurrentCustomers )
    )

New-Customers-who-attended-events-DAX2

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you Dale - Works like a charm - Robot Very Happy It is a happy Friday 🙂

My pleasure. There will be happy weekends too.

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.