Forum Discussion
Calculated column countrows in another table
I need a calculated column. I am able to get a count by using RELATED but that does not include the slicers and report filters. I need to include the report filters.
For example:
Contact table
Contact ID |
|
111 |
|
222 |
|
333 |
|
Evaluation Table
Test Number | Test Category | Contact ID |
1 | Category 1 | 111 |
2 | Category 2 | 111 |
3 | Category 3 | 111 |
1 | Category 1 | 222 |
1 | Category 1 | 222 |
2 | Category 2 | 333 |
2 | Category 2 | 333 |
1 | Category 1 | 333 |
my report filters out counting category 2 test so here are my expected results
Contact ID | count of tests (calculated column-needs to be created) |
111 | 2 |
222 | 2 |
333 | 1 |
using COUNTROWS(RELATEDTABLE(Evaluation)) in the contact table the page level filters are not applied, so this is what that calculated column returns
If you have a calculated column (physical in your data model) you can use this values to create measures for your dashboards or use it as filter...beacause your data model gives you this opportunity now.
Tell me pls, how would you like to provide your filters made on vizualization layer to your calculated column? It's just the other way around.
Calc column uses row context, not filter context.
You can tweek the results for reasons but do you really need to this in this case?
Have you tried to create this column and use the result for your dashboards?
Regards