Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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
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
@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.
User | Count |
---|---|
83 | |
77 | |
70 | |
69 | |
54 |
User | Count |
---|---|
106 | |
96 | |
90 | |
79 | |
68 |