A table of multiple values was supplied where a single value was expected, when using Distinct

powerbi dax powerbi-datasource
Caaarlos picture Caaarlos · Feb 24, 2017 · Viewed 27.3k times · Source

I have the following table:

enter image description here

I'm trying to create a new column using Dax language: 

Column = DISTINCT('Edição'[País])

But I gott this error message:

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

The expected result is:

enter ription here

Answer

Jeroen picture Jeroen · Feb 25, 2017

You have a source table with 21 rows. You want to add a column. In the end you'll still have 21 rows.

The "expected" table has 18 rows. So with only adding a column you will never get there, it's just impossible.

To get the expected output though, you can do this easily: just create a table with just Pais as values and PowerBI will automatically group. See here a (more minimal) example:

Example grouped table

PS. If you wanted to add a column -say- with the number of years the country appears in the source data, you should:

  • choose the matrix visualization
  • add "Pais" (country) for rows
  • add e.g. "Ano" (year) for values, and summarize by "Count (Distinct)"

Related questions

how to convert a Integer to Text value in Power BI

I am creating a calculated column in existing power BI report. the calculated column concatenates integer & text columns. I tried below query which give syntax error to me, = Number.ToText(table1.[RegionID]) & " " & table1.[RegionName] I tried some …

Read More
How to format new measure (not column) in power BI as percentage?

When I create a new measure with formula, i.e. sum(col1)/max(col2), it automatically converts the result into #.#% format. Strangely, it doesn't happen all the time; sometimes it just gives result #.## format. Any idea of how to format …

Read More
How to get month name from month number in Power BI?

I have Year number and Month Number in my data. How using DAX can I get the month name out of month number? In SSRS its very easy. But how to achieve that using DAX?

Read More