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)
Helper IIOK, here goes. I recreated the necessary tables through exporting samples and re-importing static Excel files that were de-identified.
Tables:
- Clients sample: fact table
- Enrollments by day sample: this is our dimension table
- Income: we pull income into our clients table from here
When you interact with the slicers, you can see that the average income calculated in the enrollments table changes, but the average income calculated in the clients table does not change.
My concern is that the enrollments by day table has 1 row per clients per day; I want the average to be calculated for each client, not for each client-day row.
Super UserIn 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)
- matan_benyishay
Thanks so much! The bi-directional filter worked. And it's good to better understand the fact and dimension tables. I was confused because the enrollments table is powering most of the slicers, but also the count of clients in most of the visuals.
- lbendlin
Don'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.
- matan_benyishay
Thanks. 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?