Forum Discussion

Helper I

6 years ago

Need to automatically distribute values between dates

Hello, I have a query like this ID | Start | End | Duration | Amount The "Amount field" is actually supposed to be spread evenly between the start and end dates, with slightly more going...

Helper I

6 years ago

Any idea why I would get this error when I apply the query changes?

OLE DB or ODBC error: [Expression.Error] The Date operation failed because the resulting value falls outside the range of allowed values..

Resident Rockstar

6 years ago

Nope. What is you source?

Could you post a screenshot of the code you have used?

jbuck2020
Helper I
6 years ago
*UPDATED* It was indeed a relationship. Once deleted, solved the problem. Now on to SUMX of distinct values, since my ID column will contain many duplicates that I do not want summed.
________________________________________________________________________________
So the above error was fixed by limiting the ids to recent (last two years') data.

HOWEVER

Now I'm getting this error
Column 'id' in Table 'flight' contains a duplicate value '220915' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

I checked to see if there were any funky relationships that had been auto-created and elimated a few that looked wrong. But still getting this error. Not sure what the "primary key of a table" means, either.

Resident Rockstar

6 years ago

Could you try this code:

Measure =
SUMX (
    ADDCOLUMNS ( VALUES ( 'Table'[id] ); "_tmpAmount"; MIN ( 'Table'[Amount] ) );
    [_tmpAmount]
)

Related content