1
votes

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

1
Calculated columns expect a scalar value per row but DISTINCT returns a table. What are you trying to achieve, be more specific in order to help you. - alejandro zuleta
I added a new image in order to be more specific. Thanks for helping me. - Carlos Porta
In the second Brazil occurrence what should appear in the calculated column? I think the easiest way to get the distinct values is creating a calculated table, if that works for you I'll post an answer. - alejandro zuleta
I don't want to add something on the missing occurences. I want to display this new column as a chart without countries names repetitions. - Carlos Porta
If you add [País] column to a chart it will automatically group the vales in distinct ones, I don't find a reason for do that in a calculated column. - alejandro zuleta

1 Answers

5
votes

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)"