Forum Discussion

wilson_smyth's avatar
wilson_smyth
Icon for Post Patron rankPost Patron
1 year ago

Date format issue between PowerBI Desktop and semantic model built on lakehouse sql endpoint

I have a lakehouse and have built a semantic model on top of this.
I have built a power bi desktop report against this semantic model.


When i drag the dimDate date field to a slicer on the report all the visuals break and i get the error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

 

Its one of the many stumbling blocks ive hit while trying use fabric, which appears to be very buggy.

On my powerbi desktop, the date is in the format of 13/01/2024, which looks to match the date column value in my dimdate table in my lakehouse.


Not sure what needs to be done here, appreciate any help and advice.

 

Can anyone advise how i resolve this?

Data Integration

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    pretty sure the system somewhere tries to interpret that as a Murican Date. 

     

    Better to use ISO 8601 throughout to avoid ambiguities like this.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi wilson_smyth ,

     

    Thanks for the reply from lbendlin .

     

    If you are storing date data in Lakehouse using the varchar type instead of the datetime type, this may cause you to encounter data conversion errors in Power BI.

     

    Please check if the date columns in the dimDate table are stored as datetime data type, if they are stored as varchar, you need to convert them to datetime.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi wilson_smyth ,

     

    Is my follow-up just to ask if the problem has been solved?

     

    If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

     

    Thank you very much for your cooperation!

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!