So there are 2 issues here.
First is that you can't execute a DEFINE statement on it's own, it does not return anything. Which is why you get the error about the result not being a rowset. You could fix this by adding an EVALUATE statement. To return a single value that calculates the
grand total for profit you could use the ROW function.
eg.
DEFINE
MEASURE PPC[profit] = CALCULATE(
VALUES(PPC[GPTV]),
FILTER(
ALL(PPC),
PPC[Month]=VALUES(DateKey[Month]) &&
PPC[Depot]=VALUES(Depot[Depot])
)
)
EVALUATE
ROW("Profit", PPC[profit] )
If you wanted to see the profit split out by another column, like the month for instance you could use a pattern like the following with the ADDCOLUMNS function
DEFINE
MEASURE PPC[profit] = CALCULATE(
VALUES(PPC[GPTV]),
FILTER(
ALL(PPC),
PPC[Month]=VALUES(DateKey[Month]) &&
PPC[Depot]=VALUES(Depot[Depot])
)
)
EVALUATE
ADDCOLUMNS(
VALUES('Date'[Month])
, "Profit"
, PPC[profit]
)
The second problem is the underlying issue with your calculation. With the above queries DAX Studio is going to return the same error about a table with multiple values being supplied. The problem here is that CALCULATE expects a function that returns a scalar
value (ie. a single value) while the
Values function returns a single column table. It gets a little bit grey here as in some circumstances VALUES() can return a scalar value because if you have a table function that returns a table with one column and one row the tabular engine will do an
implied cast of that table to a scalar value, but usually if you are using that pattern you will "protect" the VALUES call with an if expression. eg. IF( HASONEVALUE( VALUES(PPC[GPTV]) ), ... , ... )
But most of the time if you are using CALCULATE the first expression should either refer to a measure or to an aggregate function like SUM, MAX, MIN, AVG, etc.
So I'm guessing that for a profit measure you'd want to see the SUM so you should use an expression like the following:
DEFINE
MEASURE PPC[profit] = CALCULATE(
SUM(PPC[GPTV]),
FILTER(
ALL(PPC),
PPC[Month]=VALUES(DateKey[Month]) &&
PPC[Depot]=VALUES(Depot[Depot])
)
)
EVALUATE
ROW("Profit", PPC[profit] )
