cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

measure using VALUE for unique Dates

i am experimenting with DAX, VALUES() creates a table of unqiue values.  if i use UniqueDates = Values(Sales[orderdate]) and drag this on a matrix or table i get the error, "a table of multiple values was suplied where a single value was expected".

 

i know I used a measure to create this, but i also assumed by 'dont summarize' it would be a Dimension and I could use it as a list of values.

 

it VALUES() creates a table or column of unique values why do I get an error?  thanks for any help understanding

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: measure using VALUE for unique Dates

Measure will always return a scalar value, never a table, VALUES returns a table hence the error
you can use your syntax in a calculated table and it will have the behaviour you expect

EDIT - if you drag Sales[orderdate] to the visual it will only show unique values without any additional code



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Highlighted
Super User II
Super User II

Re: measure using VALUE for unique Dates

Measure will always return a scalar value, never a table, VALUES returns a table hence the error
you can use your syntax in a calculated table and it will have the behaviour you expect

EDIT - if you drag Sales[orderdate] to the visual it will only show unique values without any additional code



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Highlighted
Solution Sage
Solution Sage

Re: measure using VALUE for unique Dates

 

Measure should return single value in context, rather than scalar list.

In measure, VALUES can only be used as intermediate, to supply list to another function.

 

If you want to create list of unique from dates...

You could create new table in Modeling. And then use Table = VALUES(Table1[Date])

 

Though since Values will consider Blank as unique, I'd prefer to use DISTINCT (when there are null fields in the column).

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors