Forum Discussion
Max function doesn't work
Hi,
I have a database like this:
| object | time | sales |
| chairs | 201606 | 14 |
| chairs | 201706 | 12 |
| chairs | 201806 | 18 |
| table | 201606 | 19 |
| table | 201706 | 24 |
table
| 201806 | 23 |
Object is a text field, time is a TEXT field, sales is a number field.
I should make a formula like this:
CALCULATE (MAX(Sales), time=max(time))
but it gives me 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
how can I do?
thank you
In that case use this construct.
Measure = VAR MaxTime = MAX ( Table[time] ) RETURN CALCULATE ( MAX ( Sales[Column] ), Table[time] = MaxTime, field4 = "F" )You can use multiple boolean operations in the same filter argument of CALCUALTE when the opeartion is over the same column for example the below works fine
Measure = VAR MaxTime = MAX ( Table[time] ) RETURN CALCULATE ( MAX ( Sales[Column] ), Table[time] = MaxTime && Table[time] = "F" )becuase internally Table[time] = MaxTime && Table[time = "F" expands and becomes:
FILTER ( ALL ( Table[time] ), Table[time] = MaxTime && Table[time] = "F" )But in case of multiple columns DAX engine is unable to figure out the way to create exisiting combination of Time and the other column, so in that case you can use this:
Measure = VAR MaxTime = MAX ( Table[time] ) RETURN CALCULATE ( MAX ( Sales[Column] ), FILTER ( ALL ( Table[time], Table[field4] ), Table[time] = MaxTime && Table[field4] = "F" ) )but I am assuming field4 and time are the columns of the same table, other wise separate them into 2 filters of CALCULATE as shown in the first example
6 Replies
Giada_Togliatti , You need to use filter clause
CALCULATE (MAX(Table[Sales]),filter(Table, Table[time]=max(Table[time])))
CALCULATE (MAX(Table[Sales]),filter(all(Table), Table[time]=max(Table[time])))You can try allselected in place of all
amitchandak , I've tried the formula with all and allselected but now I have another error:
dax comparison operation do not support comparing values of type text with values of type number, consider using the value or format function to convert one of this value
how can I change the formula to delete this error?
I think the problem is that field time is in text format
thank you for your help
Giada_Togliatti , share your formula and error.
aggregation with boolean operation is not allowed, store the aggregation in a variable.
Measure = VAR MaxTime = MAX ( time ) RETURN CALCULATE ( MAX ( Sales ), Table[time] = MaxTime )AntrikshSharma Thank you for your reply,
the formula works, but now I have another problem:
if inside calculate I add another condition I have another error:
the expression contains multiple column, but only a single column can be used in a true/false expression that is used as a table filter expression,
how can I add this second condition on field 4(text)?Measure = VAR MaxTime = MAX ( time ) RETURN CALCULATE ( MAX ( Sales ), Table[time] = MaxTime && field4= "F" )In that case use this construct.
Measure = VAR MaxTime = MAX ( Table[time] ) RETURN CALCULATE ( MAX ( Sales[Column] ), Table[time] = MaxTime, field4 = "F" )You can use multiple boolean operations in the same filter argument of CALCUALTE when the opeartion is over the same column for example the below works fine
Measure = VAR MaxTime = MAX ( Table[time] ) RETURN CALCULATE ( MAX ( Sales[Column] ), Table[time] = MaxTime && Table[time] = "F" )becuase internally Table[time] = MaxTime && Table[time = "F" expands and becomes:
FILTER ( ALL ( Table[time] ), Table[time] = MaxTime && Table[time] = "F" )But in case of multiple columns DAX engine is unable to figure out the way to create exisiting combination of Time and the other column, so in that case you can use this:
Measure = VAR MaxTime = MAX ( Table[time] ) RETURN CALCULATE ( MAX ( Sales[Column] ), FILTER ( ALL ( Table[time], Table[field4] ), Table[time] = MaxTime && Table[field4] = "F" ) )but I am assuming field4 and time are the columns of the same table, other wise separate them into 2 filters of CALCULATE as shown in the first example
