I would like to create a calculated column (measure doesn't work since I want to use it in a slicer) where the value is a count of values in a related table. If I were to filter on the related table, the values in the calculated column should be updated.
Table A is basically a list of companies we do business with and some of their characteristics. Table B is a list of users working for these companies. Each user (row) in Table B has an assigned user type, let's say Type 1 and Type 2. I want to create a calculated column in Table A that counts the number of users in Table B for each company. This column should be used as a slicer in my report. I also want to create a slicer with user type, so if I select Type 1, the slicer with number of users should be updated such that only users with type 1 is counted.
I have tried COUNTROWS(RELATEDTABLE('Table B')), but if I select Type 1 in my slicer, this still counts both Type 1 and Type 2 users. The relationship between Table A and Table B is "Both", but I have tried with "Single" as well without success. This is really easy with a measure, but the measure cannot be used as a slicer.