Forum Discussion
Helper VFinding totals when no direct relationship
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?
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!
5 Replies
- Greg_Deckler
Community Champion
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. 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
aashtonAnonymous 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.
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!
