Does anyone know what's wrong with my DAX?

[GenericUser1]

This is it:

Active Case Age = calculate(filter(incidents, incidents[Case Status] = "Active"), Year(TODAY()) - incidents[Case Created On].[Day])

An error keeps coming up:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

I want to figure out the Age for Active Cases (which means tickets or incidents that occured) only instead of other cases that are resolved or canceled.  

[v-cherch-msft]

Hi @GenericUser1

You may also use MAX Function or SELECTEDVALUE Function for the [case created on] column to create the measure.

Regards,

Cherie

[Seward12533]

Your calculate is not structured correctly.  . 

The first term needs to be the expression which should then be followed by your filter expression - and you probably don't need the explicit filter but that's not causing the issue.  Assuming your second term is meant to be what your calculating that does not appear to be what your trying to do.  YEAR(Today()) will return the YEAR of the current date. and the second term indents[Case Created On].[DAY] will return the DAY of the date it was created on so if case was created on 8/20/18 your expression would  be 2018-20 = 1998 which I doubt is what  you want.

Also rather than just subtracting dates I'd recommend DATEDIFF.  

So if you wanted the number of days from TODAY and the create date for active  cases try this.   

Active Case Age = calculate(DATEDIFFE(incidents[case created on],,TODAY(),DAY), incidents[Case Status] = "Active")

IF you wanted the age in weeks

Active Case Age = calculate(DATEDIFFE(incidents[case created on],,TODAY(),WEEK), incidents[Case Status] = "Active")

[GenericUser1]

Thanks! But the DAX only returns measures not columns? Would I have to create some sort of measure for the DAX to accept the Created On column>

[Seward12533]

If you want as Measure then you need to aggregate the value.


Active Case Age = calculate(DATEDIFFE(AVERAGE(incidents[case created on]),TODAY(),DAY), incidents[Case Status] = "Active")