topic The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value in DAX Commands and Tips

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

andicornejo12 — Thu, 24 Oct 2024 08:31:45 GMT

Kindly assist on the below;

I am trying to filter one table using the column from another table (date type), however, I get the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value" when i input the below formula to create a column. How do i solve it?

FilteredProjects =

FILTER (

gold_procon_projekte_historical,

gold_procon_projekte_historical[valid_from] <= SELECTEDVALUE(gold_procon_stammdaten[stamm_daten]) &&

(

gold_procon_projekte_historical[valid_to] >= SELECTEDVALUE(gold_procon_stammdaten[stamm_daten]) ||

ISBLANK(gold_procon_projekte_historical[valid_to]) &&

gold_procon_projekte_historical[is_current] = TRUE()

)

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

mark_endicott — Thu, 24 Oct 2024 08:57:16 GMT

andicornejo12 - Try this:

FILTER ( ALL(gold_procon_projekte_historical[valid_from], gold_procon_projekte_historical[valid_to], gold_procon_projekte_historical[is_current]), gold_procon_projekte_historical[valid_from] <= SELECTEDVALUE(gold_procon_stammdaten[stamm_daten]) && ( gold_procon_projekte_historical[valid_to] >= SELECTEDVALUE(gold_procon_stammdaten[stamm_daten]) || ISBLANK(gold_procon_projekte_historical[valid_to]) && gold_procon_projekte_historical[is_current] = TRUE() ) )

Essentially I'm applying the filters to the specific columns inside the ALL(), rather than the whole table.

If this works for you, please accept it as the solution, it will helpwith visibility for others with the same problem.

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

andicornejo12 — Wed, 30 Oct 2024 13:55:22 GMT

hello mark_endicott

Thank you so much for your answer. Unfortunately I keep getting the same error message:

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

mark_endicott — Wed, 30 Oct 2024 14:29:56 GMT

andicornejo12 - Are you attempting to use this DAX in a measure?

If so, that's likely to the problem. At the table level, the result for the flag could be both TRUE and FALSE based on the row, and a measure requires a scalar value.

If you are using a measure, try this:

VAR _table = ADDCOLUMNS ( gold_procon_projekte_historical, "@flag", IF ( gold_procon_projekte_historical[valid_from] <= SELECTEDVALUE ( gold_procon_stammdaten[stamm_daten] ) && ( gold_procon_projekte_historical[valid_to] >= SELECTEDVALUE ( gold_procon_stammdaten[stamm_daten] ) || ISBLANK ( gold_procon_projekte_historical[valid_to] ) && gold_procon_projekte_historical[is_current] = TRUE () ), 1, 0 ) ) RETURN MAXX ( _table, [@flag] )

It will change your flag to a integer of 1 or 0, but it can now be applied at the row level. I have tested it and it appears to be working fine for me:

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

andicornejo12 — Wed, 30 Oct 2024 16:26:15 GMT

Thanks so much!

Yes it did work for me too 😊