It is impossible to determine a unique value for the column PowerBI

[Anonymous]

Good evening to all. I have a problem with powerbi that I can't solve: I have the following data stored there (for simplicity, I have voluntarily reduced the size of the table):

https://files.fm/u/yx9y4mrun

id: the id of a person. Here we have three persons

Name: represents an activity performed by a person. Here we have only two activities: activity 1 and activity 2

Value: the value transmitted by the person.

Date: the date and time of transmission.

As you can see, a person can send several data in one day, at different hours. Now I want a table in powerbi that represents the data as follows

• a column that represents the first date of data transmission by a person (I did this using a measure that I named "first" with the formula MIN(Donnee[Date]))
• a column that represents the last date of data transmission by a person (I did it using a measure that I named "last" with the formula MAX(Donnee[Date]))
• a column that displays for each patient the n-th day of transmission (roughly if it is day 1 or 2 or 3)

For the latter, I wanted to make a DATEDIFF(Donnee[Date]-Donnee[last], DAY) +1. But I have the following error: It is impossible to determine a unique value for the "Date" column in the "Data" table. This can happen when a measurement formula refers to a column that contains many values, without specifying an aggregation such as min, max, count or sum to get a unique result.

[AlexisOlson · Accepted solution]

You need an aggregation for each column in the measure. For example,

DATEDIFF ( MIN ( Donnee[Date] ), MAX ( Donnee[Date] ), DAY ) + 1

The reason for this is that without row context (like you have in a calculated column), Donee[Date] refers to a column rather than a single value in that column. Even if there is only a single unique value in the local filter context, you still need to use SUM/MIN/MAX/VALUES/AVERAGE/etc.