A table of multiple values was supplied where a single value expected

[butterscotch]

Hello !

i am trying to create a calculated column where the formula will checks if either the start date or end date falls within the range of the current month and returns 1 if true, otherwise 0. but an error arise 

for every row for  start date and end date, each will have different value

thank you !

[Anonymous · Accepted solution]

Hi @butterscotch ,

For VALUES() function, it will return a only one column table with distinct value.

For EOMONTH() function, it returns the date in datetime format of the last day of the month before or after a specified number of months, the two parameters it accepts are the value, not a table.

Please replace the VALUES function with SELECTEDVALUE function for you dax formula.

For more details, you could read related document:

EOMONTH function (DAX) - DAX | Microsoft Learn,

VALUES function (DAX) - DAX | Microsoft Learn,

SELECTEDVALUE function - DAX | Microsoft Learn

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kleigh]

From the definition of VALUES:

---

When the input parameter is a column name, returns a one-column table that contains the distinct values from the specified column. Duplicate values are removed and only unique values are returned. A BLANK value can be added.

---

In other words, what is happening here is that for each row of your formula, you are getting the unique values for all rows. This is probably not what you want. You probably need to be defining the variables without using VALUES.

Calculated column formulas are written as if they are working on one row at a time, not as if they are working on rows as a set. 

The code Master[Jan-24] also looks a little odd. It implies you have a column called [Jan-24] containing a date of 1 Jan 2024 for all rows. If you have done this, it isn't a good way to model data. A better way would be to use DATE function to add a literal where required. Best may be to automatically calculate the current month, but for now use DATE(2024,1,1) as the variable value.

[saud968]

can you share a sample file, remove sensitive data