Max function doesn't work

[Giada_Togliatti]

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

[AntrikshSharma]

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 )

[Giada_Togliatti]

@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" )

, @amitchandak  

[AntrikshSharma · Accepted solution]

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

[amitchandak]

@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

[Giada_Togliatti]

@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

[amitchandak]

@Giada_Togliatti , share your formula and error.