0

Query Help - Count of records in joined table that meet X criteria

I have a tables called Staff and Event Details. There is a join table called event_staff_join to show what gigs they worked. The join is "Relationship from Staff to event_staff_join (1:N)"

I would like to, from a Staff table view, add a formula column that counts the number of gigs in the current calendar year that a staffer has been on. 

I can count the total # of gigs via count('Staffing Gigs') but I can't figure out how to limit it to only this year. All my "select" attempts have failed. 

In "Event Details" i have date field called "event date".

How code do I put in the formula field in table view to show me how many gigs in the current calendar year each staff member has worked? 

2 replies

null
    • Fred
    • 7 mths ago
    • Reported - view

    you can try:

    count('Staffing Gigs'[year('event date') = year(today())])

    When you are filtering relationship fields you use the square brackets [ ]. You can also use the year() command to get the year of the 'event date' field. You could hard code "2024" after the equal sign, but by using the year() command with the today() command you make the code dynamic to always show you the current year.

    If you find that you will want to gather your records by year, by month, by quarter, etc. I would recommend you add formula fields in your 'event_staff_join' table for each of data that you need. Here is the list of date commands.So in the future you could filter by:

    count('Staffing Gigs'[yearField = year(today())])

    Then you can create charts using the monthField, or yearMonthField, or quarter, etc.

      • Firefly Team Events
      • James.3
      • 7 mths ago
      • Reported - view

       Thanks! That helped and got it working. 

Login to reply

Content aside

  • 7 mths agoLast active
  • 2Replies
  • 40Views
  • 2 Following