Reply
Frequent Visitor
Posts: 3
Registered: Wednesday
Accepted Solution

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

 

 

 

 


Accepted Solutions
Community Support Team
Posts: 6,592
Registered: ‎05-02-2017

Re: New Customers who attended events - DAX

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

View solution in original post

Attachment
Highlighted
Community Support Team
Posts: 6,592
Registered: ‎05-02-2017

Re: New Customers who attended events - DAX

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

View solution in original post


All Replies
Community Support Team
Posts: 6,592
Registered: ‎05-02-2017

Re: New Customers who attended events - DAX

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

Attachment
Frequent Visitor
Posts: 3
Registered: Wednesday

Re: New Customers who attended events - DAX

@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

 

Highlighted
Community Support Team
Posts: 6,592
Registered: ‎05-02-2017

Re: New Customers who attended events - DAX

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

Frequent Visitor
Posts: 3
Registered: Wednesday

Re: New Customers who attended events - DAX

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

Community Support Team
Posts: 6,592
Registered: ‎05-02-2017

Re: New Customers who attended events - DAX

My pleasure. There will be happy weekends too.

 

 

Best Regards,
Dale