Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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])
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!
Thx guys; you really helped me out!
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!
@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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
142 | |
104 | |
104 | |
80 | |
66 |