Why does VALUES(Sales[Channel]) function returns an error in a table visual?

[frost789]

The VALUES function returns a single-column table of unique values. I have a TestMeasure in the Key Measures measure table. When I dragged it into the table visual, I got an error. Why am I not getting the result I expect it? (which is a column showing "Distributor", "Export", "Wholesale")

https://limewire.com/d/ZP6YZ#ouJjwdr3ag

[v-karpurapud]

Hi @frost789 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[v-karpurapud]

Hi @frost789 

I would also like to thank you @GeraldGEmerick  and  @Ritaf1983 for your active participation and for sharing solutions within the community forum. 

I hope the information provided helps resolve your issue. If you have any further questions or need additional assistance, please feel free to contact us. We are always here to help.

Best regards,
Community Support Team.

[Ritaf1983 · Accepted solution]

Hi @frost789 

When you use VALUES(Sales[Channel]) inside a measure, the result is not a single value.
VALUES returns a table object (a one-column table of distinct values), and a table cannot be placed directly in a visual that expects a single scalar value.
That’s why you get an error when dragging the measure into a table visual.

A measure must always return one value, not a column.

If your goal is to display a list of the distinct channels inside one cell of the visual, wrap VALUES with CONCATENATEX, for example:

Channels =
CONCATENATEX( VALUES(Sales[Channel]), Sales[Channel], ", " )

This returns a text string, which can be shown in any visual.

If your intention is to create a physical column or a table containing the unique channels, then VALUES must be used in a calculated table, such as:

ChannelsTable =
VALUES(Sales[Channel])

This creates a proper table object that Power BI can display.

Yes — this is the correct technical reasoning:
• VALUES returns a table, not a scalar
• Measures cannot return tables
• CONCATENATEX converts the table into a usable single value
• For physical modeling, use a calculated table instead

The upfated pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[GeraldGEmerick]

@frost789 Measures can only return single values (scalars) not tables. Instead, simply put your Sales[Channel] column into a Table visual.