Why do I get this DAX error

[katieandemil]

Hi,

I would like to understand why I get an error when I try to filter date field from reporting date dimension using my fact table field Due Date. This variable SequenceResultWithError returns this error "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

I don't understand why it says the expression contains multiple columns, I don't see multiple columns here.

Here is the full DAX calculated column.

Column =

var DueDate = Forecast[Due Date]

var SequenceResultWithError = CALCULATE(max('Reporting Date'[Working Day sequence]),'Reporting Date'[Date] = Forecast[Due Date])

var SequenceResult2 = CALCULATE(max('Reporting Date'[Working Day sequence]),'Reporting Date'[Date] = DueDate)

return SequenceResult2

Take care

[v-lid-msft]

Hi @katieandemil ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[katieandemil]

I'm trying to better understand DAX and I still don't have an answer to my original question. which is why do I get an error about multiple columns if I use reference to a single column only. 

[v-lid-msft]

Hi @katieandemil ,

We can just delete the variable with error, it cannot recogenize 'Forest'[Due Date] as a constant value in filter of calculate funcion.

Column = 
VAR DueDate = [Due Date]
RETURN
    CALCULATE (
        MAX ( 'Reporting Date'[Working Day sequence] ),
        'Reporting Date'[Date] = DueDate
    )

After using a var to save the value of [Due Date] column, it will be treated as a constant value and can be used in the filter.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

you need to change

var DueDate = allselcetd(Forecast[Due Date])

or

var DueDate = maxx(Forecast,Forecast[Due Date])

As per need.

Using that column is passing all values from another table.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601