Calculated column countrows in another table

Question

Is there a way to create a calclulated column that will get a count of records from another table but keeping the report filters?I have two tablesEmployee table with an employee id and a test table with testing information with an employee id of the employee that took the tests.&nbsp;I have page leve filters and slicers on my report and I would like to add a calculated column to the employee table that counts the number of test the employee took, keeping all the filters.I have tried keepfilters but this is not working.Count of Tests= CALCULATE(&nbsp; &nbsp; COUNTROWS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TestTable,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TestTable[EmployeeId__c] = SELECTEDVALUE( 'Employee'[Employee_ID__c])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )

sergej_og · Answer

Hey&nbsp;tomperro&nbsp;,since you have (I guess) a 1:n relationship between the tables you can use RELATEDTABLE (to calculate value from the n-side of the relationship) and store the result in a calculated column.like so:

COUNTROWS(RELATEDTABLE(Sales)).You have to put the formula on the 1-side of the relationship.Regards

tomperro · Answer

That does give me the count but it is not applying the filters.
For example, I do not want to count the test number 123 for the employees.
I do not want to put the filter in the formula, I would like to use the page level and slicers already applied.

sergej_og · Answer

Calculated column works with row context.
If you would like to tweek your results you have to use CALCULATE.

What about get all data (countrows) into calculated column and then work with measures and visualizations to get your goal? You can exlude every thing you want.
It's a bit hard to understand your scenario without seeing any sample data/results.

Regards

tomperro · Answer

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 tableContact ID&nbsp;111&nbsp;222&nbsp;333&nbsp;&nbsp;Evaluation TableTest NumberTest CategoryContact ID1Category 11112Category 21113Category 31111Category 12221Category 12222Category 23332Category 23331Category 1333&nbsp;my report filters out counting category 2 test so here are my expected results&nbsp;Contact IDcount of tests (calculated column-needs to be created)111222223331&nbsp;using COUNTROWS(RELATEDTABLE(Evaluation)) in the contact table the page level filters are not applied, so this is what that calculated column returns&nbsp;

sergej_og · Answer

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

Forum Discussion

Calculated column countrows in another table

5 Replies

Related content

COUNTROWS

Countrows as a filter within a CALCULATE function

Función CALCULATE(COUNTROWS(),(FILTER)

Calculate Countrows Issue wrong number displayed

Multiple FIlter CountRows of another table