Dax Ratio across fact tables

Question

I have two star schemas that share a few dimensions:

Staff Star Schema. This contains a fact table that has the grain of "staff role". so for every role a person held in the college there is a row, with a start and end date of that role.
Students Results Star Schema. this schemas grain is a students results for a course on a yearly basis.One row per student per year with Pass or Fail.

They share a date dimension. I can select a date and see aggregates for all staff employed at that point, and all students results for the year selected.

I now also need to be able to do a ratio of staff/students at that selected date. The problem is the staff is in one schema & students in another, only sharing one dimension.

Is this even possible with dax or am i better to just do it in a stored procedure and reporting services?

user5226582 user5226582 · Accepted Answer · 2016-04-08T15:05:23

Do you have measures for staff and student count?

Something along the lines of

staffcount := countrows(
  filter(staff,
    and(
      staff[startdate]<current_day,
      staff[enddate]>current_day
    )
  )
)

studentcount := countrows(
  filter(student,
      student[year]=year(current_day)
  )
)

Then, you could have a measure for the ratio you're interested in:

staff to student ratio := [staffcount] / [studentcount]

Dax Ratio across fact tables

1 Answers