What's wrong with my syntax?

[Anonymous]

Hi all, I am looking to create a measure that will take a count of cases sold and then return the average of it, however for the life of me I cannot figure out why my syntax is off? I tried using DAX formatter and that states I am missing an equal sign after average?

VAR MaxDate = MAX ( 'Calendar'[Date] )
VAR MinDate = MIN ( 'Calendar'[Date] )
VAR SoldCaseIntCount = CALCULATE(COUNT ('Cases'[SoldCaseInt]))

RETURN
CALCULATE (AVERAGE (SoldCaseIntCount),
FILTER (ALLEXCEPT ('Cases', 'Cases'[Settlement Officer]),
'Cases'[Sale Date] >= MinDate
&& 'Cases'[Sale Date] <= MaxDate)
)

Let me know what you think...

[bongmw]

Hi @Anonymous ,

I would almost be tempted to start off with this simple measure below and then tweak as needed:

AverageCasesSold = AVERAGEX
(
Cases,
SUM('Cases'[SoldCaseInt])
)

Using variables to store dates which then gets applied to the filter seems redundant, since this happens automatically. Same with Settlement Officers, if you are analyzing this measure by Officers, the filter context gets applied automatically. I have attached a spreadsheet to illustrate what I mean.

https://drive.google.com/file/d/1GSJ7k4BovoyZIFnKt8xnOn5NO3Gzlgt8/view?usp=sharing

When you create a Pivot Table / add a Power BI visual, the filter context of the visual/table applies to the measure. The only reason you would change the filter context within your measure is if you want to overwrite the visual filter context. An example is if you need to calculate the cumulative sum, by date. You would then need to overwrite the original date and calculate the sum for all the dates before the original date

[Anonymous]

@bongmw Hi and thanks for response! I should have clarified, the SoldCaseInt is essentially a table to capture whether a case has been sold or not (so it only holds values 1 or 0). What I am trying to do is count how many 1's we have and then take the average of that.

The measure you pasted will essentially just return 1 always as thats the average of 1. 

The filter is simply to take only the cases sold by the settlement officers and between those date ranges. 

Thanks!

[bongmw]

On my point about not being able to change the values of a variable after definition, please refer to this link below, under the section Calculating a Value Before Filtering

https://radacad.com/caution-when-using-variables-in-dax-and-power-bi

[bongmw]

Hi @Anonymous ,

I presume you are trying to define a measure here.
1. DAX formatter is expecting the new measure name to be defined
2. DAX formatter does not like that AVERAGE of the variable SoldCaseIntCount.

I am sure that once the variable has been defined you cannot change the values of the variable by passing another filter context to it.

I am unsure what you mean to do with the ALLEXCEPT function but I am guessing you are trying to calculate average by Officers? Power BI should take care of this automatically if you analyze this measure by the officer. e.g. if you have 'Cases'[Settlement Officer] under the rows of a matrix visual and this measure under 'Values'

To sum it up, i think what you are trying to do is maybe:
AverageCasesSold =
VAR MaxDate =
MAX ( 'Calendar'[Date] )
VAR MinDate =
MIN ( 'Calendar'[Date] )
RETURN
CALCULATE (
AVERAGE ( 'Cases'[SoldCaseInt] ),
FILTER (
ALL('Cases'[Sale Date]),
'Cases'[Sale Date] >= MinDate
&& 'Cases'[Sale Date] <= MaxDate
)
)