powerpivot - Customer List with invoice which has all selected items -

i have dimproduct table, dimcustomer table , factsales table. there targeted product list. want list customers buy products of targeted product list in 1 invoice. how do it? have no clue. please give me advice.

customerswithalltargets:= countrows(     filter(         dimcustomer         ,calculate(             distinctcount( factsale[productkey] )             ,targetproduct         ) = distinctcount( targetproduct[productkey] )     ) ) 

let's break down.

countrows() says , counts rows in table.

the table rows want count result of our filter(). filter() takes table expression first argument, creating row context iterating on each row in table expression. each row, expression in second argument evaluated. rows in table second argument evaluates true included in output.

our table argument dimcustomer. dimcustomer filtered pivot's filter context (e.g. if select subset of customers, subset considered).

for each customer, evaluate calculate().

calculate() evaluates first argument in filter context defined second through last arguments. we're counting distinct values in factsale[productkey] current customer (current in iteration through each row filter()), subject constraint exist in table targetproduct.

we testing value of calculate() (how many target products current customer has bought), count of values in targetproduct[productkey]. when equal, customer has bought products. when not equal, customer hasn't.

so we'll return table of customers who've bought target products. @ customer level in pivot, return 1 or blank each customer. pivot tables automatically suppress display of row labels blanks measures, you'll see customers have bought target products.

the grand total tell how many customers total have bought targets.

this support selecting subsets of targetproduct, if have different target groups.

below image of model , sample data, along pivot table showing whole thing behaving appropriately.

edit

we'll make use of 1 more function able group multiple fields, summarize().

customerswithalltargets:= countrows(     filter(         summarize(             factsale             ,factsale[invoicekey]             ,factsale[customerkey]         )         ,calculate(             distinctcount( factsale[productkey] )             ,targetproduct         ) = distinctcount( targetproduct[productkey] )     ) ) 

summarize() takes table first argument , list of fields group by. can add calculated columns , rollups on columns, don't need that. newer versions of power pivot (excel 2016) have syntax-equivalent groupby() performs grouping potential (small) performance improvement. group on invoicekey , customerkey , same filtering before.

this alter nature of return value. count number of customers who've purchased whole set of target products. since we're grouping on invoice, count of number of customer-invoice pairs have targets purchased. since requirement stated listing customers, measure still meets them. may see number >1 specific customer, though. still have behavior in pivot of omitting customers don't meet criteria.

here's picture of altered sample data performing measure. note customer6 has purchased 3 targets, on separate invoices. customer3 still shows 3 on 1 invoice.