Forum Discussion
Filter by datediff in Analysis Services
Hello,
I have created a calculation
Measure = CALCULATE(
DISTINCTCOUNT([Supplier Name]),
DATEDIFF(max([Creation Date]),
TODAY(),year)==1
)
with the hope that I will get the the suppliers that were created last year etc.
Unfortunately i am working on analysis services so it does pop an error :A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
Unfortunately i can make no tables or go back to the model itself.
Any ideas?
Anonymous , Try like
Measure = CALCULATE(
COUNTX(values(Table[Supplier Name]),table[Supplier Name]),
filter(table, DATEDIFF(max([Creation Date]),
TODAY(),year)=1
))OR
Measure = countx(summarize(Table , Table[Supplier Name], "_1",
calculate(lDISTINCTCOUNT([Supplier Name]),
filter(table, DATEDIFF(max([Creation Date]),
TODAY(),year)=1
))),[_1])
4 Replies
- amitchandak
Super User
Anonymous , try like
Measure = CALCULATE(
DISTINCTCOUNT([Supplier Name]),
filter(table, DATEDIFF(max([Creation Date]),
TODAY(),year)=1
))Seems like a plausible solution amitchandak but this is what I got when I tried to use it
SO it works only when I have the field of suppliers in the view, not in the card, isn't it strange?
- amitchandak
Anonymous , Try like
Measure = CALCULATE(
COUNTX(values(Table[Supplier Name]),table[Supplier Name]),
filter(table, DATEDIFF(max([Creation Date]),
TODAY(),year)=1
))OR
Measure = countx(summarize(Table , Table[Supplier Name], "_1",
calculate(lDISTINCTCOUNT([Supplier Name]),
filter(table, DATEDIFF(max([Creation Date]),
TODAY(),year)=1
))),[_1])