Filtering based on several columns

[Anonymous]

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.

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

AverageX(Summarize(Table, Table[PERSONID], [NCLIENT]), [NCLIENT])

[amitchandak]

@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

https://radacad.com/pivot-and-unpivot-with-power-bi

[Anonymous]

I've done that, but the trouble is Person 1 belongs to two teams. So, if I am generating an "overall" average number of clients (i.e., without selecting a team on the slicer) that person gets counted twice in the computation of the average.