Direct query, transformation and filters

[Anonymous]

Power BI is connected to Azure SQL Server in direct query mode as the only data source which has below data.

In pbix, are two table visuals: Users and Payload. Users visual displays `userId` and `timeAt` fields. Payload visual displays `payload` as per selected row in Users visual. Below are two pbixes, first works, second one does not. The difference is that, in working pbix, I transformed datetimeoffset field (timeAt) to text in data source transformation.

1. Working pbix - https://1drv.ms/u/s!AqNol4ePfff6ghnTXcFrhiJQvtSi?e=A8f2Xk

2. Non working pbix - https://1drv.ms/u/s!AqNol4ePfff6ghgidvIOt-Xo-qTn?e=nDyyit

Now, How can I avoid this? I don't want to transform datetimeoffset to text as that impacts readability of timeAt field. How should I approach this issue? I assume the issue is since filter on Payload visual is getting impacted by timeAt field. An alternate option: since `userId` is a primary key, I am ok to using `userId` alone as filter to Payload visual. Is it possible?

I had initially observed this issue in ADX as part of bigger pbix. This is a MCVE to demonstrate the isssue. Credentails to access the data source in pbix are username/password as myuser/myus3r#7

[Anonymous]

Closing the query since there are no answer. I am also deleting the SQL server instances provisionied in Azure. Further readers will not be able to verify the shared pbix any more. I will also not reply to further queries under this post. Kindly proceed as the moderator of this forum feel appropriate.

[Anonymous]

Thanks for the reply. But, no. That did not help.  Also, the pbix (and the credentials needed) is already shared in the original post (OP) itself. I am maintaining the demo SQL DB just for the users of this forum/question.

Now coming to suggestion of change data type :- Humbly requesting to take a second look at the query. It had two pbix. Working and Not working. The working pbix has infact changed the data type to text. The non working pbix has the data type in DATETIME itself. I don't want to change data type to TEXT since that impacts sorting by date. This is already mentioned in second paragraph of OP.

[v-stephen-msft]

Hi @Anonymous ,

A pbix file with Direct Query connection can't be opened by others. It will ask for relevant credentials to open.

Best Regards,

Stephen Tao

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

[Anonymous]

I am sorry to tell this. But have to tell. Have you read the query carefully atleast once? This is the second time I am asking you to read the query carefully. The credentials are already in the original query. I am not disappointed since there is no answer, that is ok. But I am disappointed since the kind of replies I am getting here appears like `checking if original poster is alive, if no we can close this ticket`

[v-stephen-msft]

Hi @Anonymous ,

I'm very sorry, I think I let you down, I will check it carefully.

Best Regards,

Stephen Tao

[v-stephen-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Stephen Tao

[v-stephen-msft]

Hi @Anonymous ,

You can manually change the data type, or you can turn on the automatic detection of column data type and title. You can do these operations in the Power Query Editor.

Please kindly refer to:

https://docs.microsoft.com/en-us/power-query/data-types

Best Regards,

Stephen Tao

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