Filtering mulitple lists with one slicer

[Wannes]

Hi community,

I want to make a report on a single table of data. Due to privacy reasons, I can not share it, but the relevant columns are a groupname column and a username column (both text columns). One user can belong to multiple groups. 

The report I want to make contains a single slicer where N (up to 10) groups are selected, N lists and a pie chart. Each list contains the users belonging to one group. So, if group A is selected, there is one list and it contains the users of group A. If groups B and C are selected, one list has all users of group B and another list has all users of group C.

So far, I've made 10 measures that each have one group, made with the following formula:

Group1Selected = IF(COUNTROWS(VALUES(MyTable[groupname])) >= 1, INDEX(1, VALUES(MyTable[groupname])), BLANK())

This part of my problem works fine, and each measure contains exactly one group. However, I wanted to include a calculated column for each selected group that is 1 if the group of that row is the selected group and 0 if it's not. So, col1 is 1 when the Group1Selected measure is equal to the group in that row. I tried to use 

Filter1 = IF(Mytable[groupname] = [Group1Selected], 1, 0)

but this yields 1 for all rows, in all cases. When I make another column with Group2Selected, it always yields 0. How can I solve this problem?

[Anonymous]

Hi @Wannes 

Actually that the slicer and filter can not affect the calculated columns in table view.

The value of a calculated table or calculate column is computed during data refresh, it does not depend on user interaction in the report. Since calculated column is static, it could not be dynamically changed based on the selection in slicer.

By the way, measure in calculated column could be seemed as static, In other words, sections in report view can not affect the table view.

For your case, please create measure instead of calculated columns.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Wannes]

Hi, thanks for your answer! I don't think this is possible for this report. A measure can only contain one value as far as I know and in all solutions I tried. Since I need all users in one group, I don't think I can make a measure that contains all users. If that is possible, please let me know how.