Forum Discussion
Filtering based on several columns
I have some data structured as below - rows are individuals; each individual can belong to one or more of teams A through D, and each invidual has a certain number of clients. I'd like to create a slicer that selects a team. Let's say I have a card which computed the average number of clients.
| PERSONID | N_CLIENTS | TEAM_A | TEAM_B | TEAM_C | TEAM_D |
| 1 | 6 | TRUE | TRUE | FALSE | FALSE |
| 2 | 12 | FALSE | TRUE | FALSE | FALSE |
| 3 | 4 | FALSE | FALSE | FALSE | TRUE |
| 4 | 9 | TRUE | FALSE | TRUE | FALSE |
| 5 | 4 | FALSE | TRUE | FALSE | TRUE |
| 6 | 1 | TRUE | FALSE | FALSE | FALSE |
| 7 | 20 | TRUE | FALSE | FALSE | FALSE |
| 8 | 14 | FALSE | TRUE | FALSE | TRUE |
The googling I have done suggests that I unpivot the team rows. But if I do that, I end up with each individual being listed more than once on the table and so my card is incorrect when no selection is made on the slicer.
So, what I'm looking for is either a way to do this filter with the data in "wide" format OR a way for my card to only consider one row per individual with the data in "long" format.
Anonymous , Try a measure like
AverageX(Summarize(Table, Table[PERSONID], [NCLIENT]), [NCLIENT])
3 Replies
Anonymous , Unpivot and remove FALSE rows(Power query filter TRUE) , I can see most of the row has one true that will remove duplicate and you do not filter = True
Anonymous , Try a measure like
AverageX(Summarize(Table, Table[PERSONID], [NCLIENT]), [NCLIENT])