Multiple columns error?

[rmcgrath]

I have a measure called MAX DATE.  It is simply:  MAX DATE = MAX(Dates Table[Date])

I then wanted to use that in the following two DAX measures to see what the results would be:

1)  Test1 = FILTER(ALL('Dates Table'),'Dates Table'[Date]<=[Max Date])

2)  Test2 = FILTER(ALL('Dates Table'),'Dates Table'[Date]<=MAX(Dates Table[Date])

Unfortunately, both generate the "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." error.

Can you explain:

1.  Why the error?

2.  What would be the difference between the two (if they worked)?

Here is a screenshot of my Dates Table:

[tharunkumarRTK]

@rmcgrath 

1.  Why the error?

There is a problem in your measure. A measure result should be a scalar value, either numeric or text. But it cannot return a table.

FILTER function is a table function, which means it returns a table not a scalar value. So you want to use Measures then one option that you have is to use COUNTROWS function. 

1)  Test1 = COUNTROWS(FILTER(ALL('Dates Table'),'Dates Table'[Date]<=[Max Date]))
2)  Test2 = COUNTROWS( FILTER(ALL('Dates Table'),'Dates Table'[Date]<=MAX(Dates Table[Date]))

What would be the difference between the two (if they worked)?

If you want to see the results of your filter functions then you need to create tables. Create two tables, Test1 and Test2 and observe the results. The difference would be more evident if you put < rather than <=. 

In Test1 you used a measure [MAX DATE] and kept it insider a filter function which applies row context, and because [MAX DATE] is a measure, row context will be transitioned into filter context. (context transition).

so for <= you will see all records in the date table.

for <, you result table will be empty.

In Test2 there wont be anny context transition,  so

for <= you will see all the records

for < you will see all the records except the record with maximum date. I create the code snippet in DAX.DO and you can test it there 

https://dax.do/0mNnb3xQobMy0s/

I would suggest you to go though this blog read about context transition

https://www.sqlbi.com/articles/understanding-context-transition-in-dax/

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[shafiz_p]

Hi @rmcgrath  

Measure always return scalar value. But your formula used a filter function which returns a table. So without any aggregation, if you use this , you will get such error.

You know that filter works row wise, that means, return all the rows from the specified table which meets the given condition. In your case, condition will return all the rows which less than max date.

So to fix, use aggregation function such as countrows. This function will give you result, how many dates are less than max dates.

Hope this clearify your thoghts!!

If this, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[DataNinja777 · Accepted solution]

Hi @rmcgrath ,

The error you’re encountering comes down to how DAX interprets scalar and table values within FILTER. Let’s break down the issues and the differences between the two formulas if they were valid.

In DAX, the FILTER function expects its second argument (the condition) to evaluate to a scalar (single value) for each row in the table context. Here’s how this causes issues in your formulas:

• FILTER(ALL('Dates Table'), 'Dates Table'[Date] <= [Max Date]): [Max Date] is a measure, which returns a single value. However, the left side of the condition ('Dates Table'[Date]) is a column reference, which implies multiple values. This results in a conflict because DAX cannot compare multiple dates in the column to the single scalar result of [Max Date].
• FILTER(ALL('Dates Table'), 'Dates Table'[Date] <= MAX(Dates Table[Date])): Here, MAX(Dates Table[Date]) is trying to calculate the maximum date in each row context, but FILTER is iterating over all rows in 'Dates Table', which means MAX(Dates Table[Date]) is evaluated over multiple rows, causing the same "multiple columns" error.

In short, DAX doesn’t know how to handle this mixed comparison of columns and measures within FILTER.

To filter dates up to the maximum date across all rows:

Test1 = CALCULATE(
    MAX('Dates Table'[Date]),
    FILTER(
        ALL('Dates Table'),
        'Dates Table'[Date] <= [Max Date]
    )
)

To dynamically apply a date filter using the row context, you could replace MAX(Dates Table[Date]) with a variable that represents the maximum date dynamically within the desired context:

Test2 = 
VAR MaxRowDate = MAX('Dates Table'[Date])
RETURN
    CALCULATE(
        MAX('Dates Table'[Date]),
        FILTER(
            ALL('Dates Table'),
            'Dates Table'[Date] <= MaxRowDate
        )
    )

These alternatives should avoid the scalar error and give the desired filtered results.

Best regards,