Forum Discussion
filter error: a single value for column 'customer' in table 'orders' cannot be determined.
filter error: a single value for column 'customer' in table 'orders' cannot be determined. This can happen when a measure formula refers to a column that contains many values.
I'm trying to create a measure to count disctinct values of matching rows with filter conditions.
count = CALCULATE(DISTINCTCOUNT(orders[Customer]),
FILTER(orders,orders[product]="B"||orders[product]="C"),
FILTER(table1,table1[Customer]=orders[Customer]))Here there's a problem with the second FILTER function, when I tried to put [Customer] it is greyed out. How could I fix this?
The table 1 refered in the the measure is this:
table1 = SUMMARIZE (filter(orders,orders[product]="A" && orders[date]<DATE(2021,12,1)), orders[Customer], "Sales", SUM (orders[sales])) HI Anonymous,
I'd like to suggest you create a variable to extract the current order table customer list and use it as the condition in the second filter:
count = VAR currCustomer = VALUES ( orders[Customer] ) RETURN CALCULATE ( DISTINCTCOUNT ( orders[Customer] ), FILTER ( orders, orders[product] IN { "B", "C" } ), FILTER ( table1, table1[Customer] IN currCustomer ) )Regards,
Xiaoxin Sheng
2 Replies
- amitchandak
Super User
Anonymous , try like
count = CALCULATE(DISTINCTCOUNT(orders[Customer]),
FILTER(orders,orders[product]="B"||orders[product]="C"),
FILTER(values(table1[Customer]),table1[Customer]=max(orders[Customer]))) HI Anonymous,
I'd like to suggest you create a variable to extract the current order table customer list and use it as the condition in the second filter:
count = VAR currCustomer = VALUES ( orders[Customer] ) RETURN CALCULATE ( DISTINCTCOUNT ( orders[Customer] ), FILTER ( orders, orders[product] IN { "B", "C" } ), FILTER ( table1, table1[Customer] IN currCustomer ) )Regards,
Xiaoxin Sheng
