topic Re: Filtering based on several columns in DAX Commands and Tips

Filtering based on several columns

Anonymous — Thu, 17 Feb 2022 04:52:36 GMT

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.

Re: Filtering based on several columns

amitchandak — Thu, 17 Feb 2022 05:26:40 GMT

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

Re: Filtering based on several columns

Anonymous — Thu, 17 Feb 2022 05:31:08 GMT

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.

Re: Filtering based on several columns

amitchandak — Thu, 17 Feb 2022 05:36:09 GMT

Anonymous , Try a measure like

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