topic Re: Time formating for Data Model cannot be changed when using external data set in Service

Time formating for Data Model cannot be changed when using external data set

Anonymous — Wed, 17 Mar 2021 11:08:07 GMT

Hello,

I'm working with a dataset file, and separate file containing visuals. Recently I've run upon an issue with formating of the field when tried to build relations between custom Date table and another table.

So the date table and the field itself was created by powerQuery, and the column is set as date.

In the dataset file I've build an relation with one of the fields of main table. Also I've checked that that the formating of the field is Date here

I've refreshed and updated the dataset on the server, but after refreshing the file that is using this dataset i see that it is date/time field instead.

Also here the formating section is greyed out so it seems this can be only changed on the dataset, which I did in the first place.

Sadly having date/time here affects one of the visuals which is not showing correctly.

Re: Time formating for Data Model cannot be changed when using external data set

aj1973 — Wed, 17 Mar 2021 15:10:26 GMT

Hi Anonymous

It is not clear where the issue is in here however one of many rules to follow in developing reports in Power bi (for better performance) is : when you have a column that contains date and time together it is better off to split the column into 2 columns DATE, TIME.

Try it may be it could solve your issue.

Re: Time formating for Data Model cannot be changed when using external data set

collinq — Wed, 17 Mar 2021 15:32:34 GMT

Hi Anonymous ,

Is it the "Created Date" in the non-DateDim that is showing as DateTime? And, in Power Query editor, when you select that column for "Created Date", are you saying it does not let you change that to "Date"? Is the non-DateDim table DirectQuery or is it Import?

Re: Time formating for Data Model cannot be changed when using external data set

Anonymous — Thu, 18 Mar 2021 07:50:59 GMT

Hi collinq

The "Created Date" column is calculated column and it is also set to Date type

aj1973

As you see above all the fields involved are of date type, and i guess it is a feature of data model editor that you can go in and chose the formating for the field/relation for some reason by default it is date/time.

Re: Time formating for Data Model cannot be changed when using external data set

aj1973 — Thu, 18 Mar 2021 12:46:07 GMT

Anonymous

Sorry, but why did you add a new column date (Created date) from an already existing column (Created_timestamp)!?

Re: Time formating for Data Model cannot be changed when using external data set

Anonymous — Fri, 19 Mar 2021 06:49:32 GMT

aj1973

Its because I wanted to use additional date table, but on the other hand I got alot of visuals using timestamp field with date hierarchy feature. If I would use timestamp field as an foregin key for date table it cannot be used anymore as a date hierarchy field.

Of course in the long run I could switch to the date table, but so far I'm having issues with getting it to work for one visual.