Desperate in setting the right filter!

[Anonymous]

Hi Community,

I'm desperately looking into a way of having appropriate filters in a matrix table set. This is my problem:

I have a matrix table that contains customer's names (row) and 2 columns representing the revenues of a new and old product. Customers not necessarily buy one product exclusively; once filtered on e.g. the new product it should show also the revuene of the old product (if available). In my ideal world I have the ability to choose the following 3 conditions: 1) Show customers only buying new product, 2) show customers currently buying both products and 3) show customers only buying old products.

Any suggestions? 

Many thanks in advance!

BTW: I'm a noobie in PBI and pls forgive me in asking this basic question.

[amitchandak · Accepted solution]

@Anonymous , Create measures like these. Assumes new and old are product type

count of customer buying only new product = countx(filter(summarize(Table, table[customer], "_1", distinctCOUNT(Table[Product Type]), "_2", calculate(distinctCOUNT(Table[Product Type]), filter(Table, Table[Product Type]="New"))+0), [_2]>0 && [_1] =[_2]),[customer])

count of customer buying only old product = countx(filter(summarize(Table, table[customer], "_1", distinctCOUNT(Table[Product Type]), "_2", calculate(distinctCOUNT(Table[Product Type]), filter(Table, Table[Product Type]="Old"))+0), [_2]>0 && [_1] =[_2]),[customer])

count of customer buying both products = countx(filter(summarize(Table, table[customer], "_1", distinctCOUNT(Table[Product Type])), [_1] =2),[customer])

[Anonymous]

Thx guys; you really helped me out!

[littlemojopuppy · Accepted solution]

Hi!  You can easily determine customers buying old only, new only or both with table manipulation functions.  You can create three measures...each will have identical variables and the only difference will be which of the three statements you use after the RETURN statement.

VAR	CustomersBuyingOld =
	CALCULATETABLE(
		VALUES(Table[CustomerID]),
		FILTER(
			Table,
			Table[Product[ID] = [OldProductID]
		)
	)
VAR	CustomersBuyingNew =
	CALCULATETABLE(
		VALUES(Table[CustomerID]),
		FILTER(
			Table,
			Table[Product[ID] = [NewProductID]
		)
	)
RETURN

-- For Customers Buying Old Only
EXCEPT(
	CustomersBuyingOld,
	CustomersBuyingNew
)

-- For Customers Buying New Only
EXCEPT(
	CustomersBuyingNew,
	CustomersBuyingOld
)

-- For Customers Buying Both Old & New
INTERSECT(
	CustomersBuyingOld,
	CustomersBuyingNew
)

 Note that the DAX above will return tables.  If you need customer counts, place the EXCEPT() or INTERSECT() inside COUNTROWS().  If you need sales for each group of customers you can do something like this for which group of customers you need.

CALCULATE(
	SUM([Sales]),
	INTERSECT(
		CustomersBuyingOld,
		CustomersBuyingNew
	)
)

Hope this helps!

[amitchandak · Accepted solution]

@Anonymous , Create measures like these. Assumes new and old are product type

count of customer buying only new product = countx(filter(summarize(Table, table[customer], "_1", distinctCOUNT(Table[Product Type]), "_2", calculate(distinctCOUNT(Table[Product Type]), filter(Table, Table[Product Type]="New"))+0), [_2]>0 && [_1] =[_2]),[customer])

count of customer buying only old product = countx(filter(summarize(Table, table[customer], "_1", distinctCOUNT(Table[Product Type]), "_2", calculate(distinctCOUNT(Table[Product Type]), filter(Table, Table[Product Type]="Old"))+0), [_2]>0 && [_1] =[_2]),[customer])

count of customer buying both products = countx(filter(summarize(Table, table[customer], "_1", distinctCOUNT(Table[Product Type])), [_1] =2),[customer])