Forum Discussion
Helper IICalculating averages on per-record basis
In fact your Clients table is the dimension, and the other two are the fact tables - as you show in your data model.
Therefore you should use columns from the Dimension table to control your visuals. However when you select a ProgramName from the Enrollments table that selection will not reflect back into the Clients table. You can choose to change the link to bidirectional
so that filters in the fact table can flow back up into the dimension table. Or you could create a measure that senses all the filters in the fact table and then computes the result in the dimension table.
Attached is the example with the bidirectional filter. See if you can get the other (better) version to work (best done via TREATAS)
Super UserDon't get used to the bidirectional filter too much - it's not considered best practice and should only be used when it has no adverse side effects, or when you have no other choice. General guidance is that dimension tables influence (filter) fact tables, and the other way round should be avoided if possible.
Helper IIThanks. In general, the client table is providing dimensions for the enrollments table, such as a count of clients enrolled (enrollment table) by gender (clients table). The exception is the income, which comes from a different table. Would it be preferrable to have the income table flow directly to the enrollments table, instead of through the clients table?
- lbendlin
That depends on the business question you want to answer with your report. Most of the time you want to keep your fact types separate.
- matan_benyishay
OK, if we use the TREATAS function instead, I want to check my understanding. Is the right approach:
measure =
calculate(
<summary messure>,
treatas(
values(<identifier in fact table>),
<identifier in dimension table))- lbendlin
Yes, that would be the approach.