Finding totals when no direct relationship

[aashton]

Hello,

I have a People table with Email and Name.  People links to an Events table, one to many, on Email.  People also links to an Applications table one to many on email.  My user wants to attribute a person applying for a job to the specific event they went to.  So by Event, I need to list how many applications were submitted.  So for example, 3 people attended Event A, they all ended up submitting 1 application, so total 3 Applications for Event A. 

But.....The Event has a date and the Application has a Date...the application will only count towards that Event if the Application Date is greater than the Event date.   And if a person attends 3 EVents, all with a date less than the application date, the application gets attributed toward the last event they attended.

I've tried so many different scenarios to accomplish this and am stuck.  Curious what the correct data set-up for this is, and how to go about it?

[v-jingzhan-msft]

Hi @aashton 

Sorry, does the following example have a conflict or a mistake? Should it be "greater than" instead of "less than"? As you said "the application will only count towards that Event if the Application Date is less than the Event date." 

"And if a person attends 3 EVents, all with a date less than the application date, the application gets attributed toward the last event they attended." 

If I understand it correctly, an application should always be earlier than the event of it. Right? 

Best Regards,
Jing

[aashton]

@v-jingzhan-msft  Yes, you are right, I apologize I mis-typed.  The Application should come after the Event.  That's how they want to determine if an Event was a success, a bunch of people applied for a job.

[v-jingzhan-msft · Accepted solution]

Hi @aashton 

Sorry for the late reply. You can add two calculated columns to the Events table with below DAX:

Applied ? = 
VAR _email = Events[Email]
VAR _date = Events[Date]
RETURN
COUNTROWS(FILTER(Applications,Applications[Email]=_email && Applications[Apply Date]>_date))

Refined Applied ? = 
VAR _email = Events[Email]
VAR _latestEventDate = MAXX(FILTER(Events,Events[Email]=_email && Events[Applied ?]>0),Events[Date])
RETURN
IF(Events[Date]=_latestEventDate,1)

Then create a measure to sum the applications for events. 

No. Of Applications = SUM(Events[Refined Applied ?])

Hope this will be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[aashton]

@v-jingzhan-msft Thank you so much.  I know it's been a while, but I just got back to it.  That seems to be working.  I am now trying to figure out the second part to this.  Once a person applies for a job, they can then be offered a contract.  Applications links to Contracts on Candidate ID.  So I now need to find the total number of contracts per event.  Similar rules, the contract has to come after the application date, and the persons max event gets attibuted to the contract (as long as it is less than the contract date).  For this I merged queries on Applications and Contracts (left join), and have teh contract ID and date in the Applications table.  I thought that might be easier, but I'm still confused.

[Greg_Deckler]

@aashton Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.