DAX Help

[sv12]

Hi,

I have 2 tables as shown below:

I am looking to calculate a measure for number of events attended by each contact before the 1st purchase made, i.e 

distinctcount(eventname) where [FirstPurchaseDate] > [Event date] at the row level context.

I tried using the "Calculate" function, but I am not able to use the dates from 2 different tables in the filter section.

I am new to writing script in DAX. I greatly appreciate any help.

Thanks

[Anonymous · Accepted solution]

Assuming the table on the left is Table1 and the table on the right is Table2...

This measure belongs in Table1.  You will use the ContactID from Table1 in your visual.

Try this:

# Events Before First Purchase = 
// Get the current ContactID based on the filter context
VAR Current_ContactID =
	SELECTEDVALUE(Table1[ContactID])

// Get the FirstPurchaseDate for that ContactID
VAR PurchaseDate = 
	CALCULATE(
		MAX(Table1[Purchase Date])
		,Table1[ContactID] = Current_Contact)

// Get ALL the records in Table2 for the given ContactID 
// that happened before the purchase date
VAR EventsAttended_ALL = 
	FILTER(
		Table2
		,Table2[ContactID] = Current_ContactID
			&& Table2[EventDate] < PurchaseDate
	)

//Get the unique list of Table2[EventName] based on EventsAttended_ALL
VAR EventsAttended_DISTINCT = 
	CALCULATETABLE(
		VALUES(Table2[EventName])
		,EventsAttended_ALL
	)

RETURN
COUNTROWS(EventsAttended_DISTINCT)

[AlB]

Hi @sv12 

Try this:

1. Create a 1-many unidirectional relationship between PurchasesTable and EventsTable through ContactID.

2. Place PurchasesTable[ContactID] in the rows of a matrix visual. 

3. Create this measure and place it in values of the matrix

NumEvents =
CALCULATE (
    DISTINCTCOUNT ( EventsTable[EventName] ),
    FILTER (
        ALL ( EventsTable[EventDate] ),
        EventsTable[EventDate] < SELECTEDVALUE ( PurchasesTable[FirstPurchaseDate] )
    )
)

[sv12]

@AlB Thanks for the response.

I have tried the suggested solution and the following error is being thrown:

Any thoughts on that?