FIRSTDATE() Error

[JpTall]

"A date column containing duplicate dates was specified in the call to function 'FIRSTDATE'. This is not supported."

I am trying to get the first outcome date for a referral. I have an OUTCOME table and a REFERRAL table both joined 1:1 using referral referances. These are unique column with no duplicates yet I still get the error above.

Furthermore - The error seems to be resolved when I remove duplicates from the appointment reference column (there are no duplicates other than nulls and I want nulls included). Not sure why this fixes it as It has nothing to do with the relationship - Is there something I'm missing?

Any support appreciated.

[Syndicate_Admin]

Hello, the same thing happened to me and I could solve it from Power Query, modifying the Format Type from "Date / Time" to simply "Date". Then I generated the calendar seamlessly with the Calendar feature

Hopefully that's it!

Best regards

[Anonymous]

Hi@JpTall,

Do you use the time intelligence function with a calendar table? If not, it can also cause this error.

Did this error message occur when you tried to graph it? If so，after you have dragged the new column into a graph in Power BI, you can try setting the way it aggregates to “Do Not Summarize".

Hope it helps.

Best Regards,
Caitlyn Yan

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

[VijayP]

@JpTall 

To get the outcome why can't you use Min() Function instead of FirstDate?!

[JpTall]

When I use MIN() it generates the 1st date from the entire table for each record in my new column (all the same date). But I want the first date from each referral in that table to be added into the column.

Usually the join works here. I have also removed dupliaces from the join column but it doesn't help.

[PC2790]

You can use the logic in such a way that it gets the FirstDate if there is no blank value, something like

Calculated column = if(not(ISBLANK(YourColumn)),<FIRSTDATE application>,BLANK())

This was the formula will not break because of blanks and the values will also stay intact

If you share our sample dataset, a more precise version of the formula can be provided

Thankyou

[JpTall]

Sorry it's not letting me paste in an example of the data.

[JpTall]

It still doesn't like the FIRSTDATE application in that formula.

[PC2790]

Would it be possible for you to provide your pbix file removing any sensitive data if there is any?

[JpTall]

I'm just looking at anonomysing that.

For the time being, how would I attach that here?

Thanks