topic Re: Can not modify power bi measure to Date Format in DAX Commands and Tips

Can not modify power bi measure to Date Format

razamatazzz — Tue, 26 Jul 2022 11:34:04 GMT

Hello All, I have created a power BI report which is embedded into D365. I created a measure which would format the date based on the location the user is i.e DD/MM/YYYY or MM/DD/YYYY. I need to do this in DAX, because i need to get the location information from the APP (D365 FO) as this is an embedded report. (can not get this from the power bi workspace)

All is working fine, however the measure is coming back as a text and not allowing me to format it to Date, which does not allow to sort it properly especially when the date format is DD/MM/YYYY.

I have tried changing the measure twice but still no luck . I have tried using Format function and date function (please see below)

val1 is the value coming back from FO

val2 is the date that i am trying to format.

Any help would be DEEPLY appreciatted

Re: Can not modify power bi measure to Date Format

tamerj1 — Tue, 26 Jul 2022 12:20:30 GMT

Hi razamatazzz
FORMAT functions return string data type. For sorting you need to use a sorting measure "the SELECTEDVALUE ( Table[Date] " will be jsut fine, and the hide it.

Re: Can not modify power bi measure to Date Format

razamatazzz — Tue, 26 Jul 2022 12:36:01 GMT

Thanks Tamerj but i need to either use format or the date function to change the format of the Date to dd/mm/yyyy incase the location is based out of the US. Are you saying i can combine the format and selectedvalue formula ?

Re: Can not modify power bi measure to Date Format

tamerj1 — Tue, 26 Jul 2022 12:37:40 GMT

razamatazzz

I'm saying you have to have two measure. One eith FORMAT to disply and one with SELECTEDVALUE to sort.

Re: Can not modify power bi measure to Date Format

razamatazzz — Tue, 26 Jul 2022 12:49:17 GMT

I dont think that will work as i have three of these measures and i want the user to be able to sort using the table headers dynamically if they want to

Re: Can not modify power bi measure to Date Format

v-chenwuz-msft — Fri, 29 Jul 2022 10:30:28 GMT

Hi razamatazzz ,

It seems impossible to do that. If possible, you can use format([date]," en-US mm-dd-yyyy") and use sort it by column to sort it. Because you can not identify 01/04/2022, it is January or April.

Best Regards

Community Support Team _ chenwu zhu

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