Forum Discussion

JpTall's avatar
JpTall
Frequent Visitor
5 years ago

FIRSTDATE() Error

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

Need Help
Show and Tell

9 Replies

  • PC2790's avatar
    PC2790
    Community Champion
    5 years ago

    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's avatar
      JpTall
      Frequent Visitor
      5 years ago

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

      • PC2790's avatar
        PC2790
        Community Champion
        5 years ago

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

    • JpTall's avatar
      JpTall
      Frequent Visitor
      5 years ago

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

  • VijayP's avatar
    VijayP
    Community Champion
    5 years ago

    JpTall 

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

    • JpTall's avatar
      JpTall
      Frequent Visitor
      5 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HiJpTall,

     

    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.

     

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    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