Filtering dynamically with slicer selections

[Anonymous]

Hello,

This is maybe a bit of a tricky one. I have a dataset which counts unique people aggregated by demographic and purchasing variables:

Age group	Sex	Apples	Orange	Pears	Peaches	Kiwi	N
10-19	Female	1	0	1	0	0	32
20-29	Male	0	0	0	1	0	46
40-49	Female	1	1	1	0	0	7
20-29	Female	1	0	1	0	1	18
30-39	Male	0	1	1	0	1	33

(e.g. 32 females aged 10-19 purchased Apples and Pears).

I'd like to create a slicer where users can select multiple values for the listed fruits (Apples, Oranges, Pears, Peaches and Kiwi), then count the total number of unique people (N) who purchase at least one of the selected fruits. (e.g. rows 1,4,5 and 6 all purchased apples and/or kiwis, so if a user selects Apples and Kiwis, I will sum(n) on all those rows). 

Most guides I've found have suggested unpivoting the data table, but if I do this it will cause aggregate groups that have more than one of the selected fruits to be duplicated in the rows, which will make my sums of [n] inaccurate. 

Some other work arounds I've considered or attempted are:

• Import the data unaggregated and unpivoted. I'd prefer to avoid this as the table will be tens of millions of rows, and is slightly less private/secure. 
• Dynamically write a sum if statement in dax which uses the slicer selections (from what I can tell, this isn't possible, particularly if the slicer is set as multiselect). 
• Create a calculated column which draws on a measure that concatonates the slicer selections and uses CONTAINSSTRING() to generate a flag I can use for filtering (I thought this worked, but it appears that the calculated column doesn't update dynamically when the slicer selections change). 

I'm at a loss what to attempt next, and hoping someone has a good idea!

[danextian]

Hi @Anonymous ,

As always, please post a sample data that can be copy-pasted to Excel as a table (not an image).

[Anonymous]

Hi @danextian, apologies, I have changed the format of the table so that you should now be able to copy and paste it

[danextian · Accepted solution]

Hi @Anonymous ,

I would still unpivot my table but make sure that each row is counted only once. To do that, I'd craete an index column in the query editor prior to unpivoting as a unique reference to the original set of rows and then use the measure below

Unique Count = 
SUMX ( SUMMARIZE ( 'Table', 'Table'[Index], 'Table'[N] ), [N] )

Please see attached pbix for the details

[Anonymous]

Thanks @danextian , I think this solves my problem!