Extract AM / PM from Datetime format in direct query

[askspepsi]

hi all,

Greetings,

Is the way to Extract AM or PM from Datetime format in direct query without changing to Import storage.

Or is there any another way to create another table or colume to extract time and convert to AM / PM

[dax · Accepted solution]

Hi @askspepsi , 

You could try to refer to @Greg_Deckler 's measure, and change it  and apply this in table visual like below

Measure = IF(RIGHT(MAX('Table1'[date]) & "",2)=SELECTEDVALUE('Table'[time]), 1,0)

Best Regards,
Zoe Zhi

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

[Greg_Deckler]

@askspepsi Should be able to use DAX to parse that using RIGHT, like maybe:

Measure = RIGHT(MAX('Table'[Datetime]) & "",2)

[askspepsi]

@Greg_Deckler  thanks for the update.

iam really sorry i was not clear in the question i asked, let me rephrase again.

i have column name with datetime (see the below) and create AM / PM when i select AM the 06:00 and 5:50 only and when i select PM i what 17:58 and 17:59 

2020-07-23 17:58:57.000

2020-07-23 17:59:02.000

2020-07-23 06:00:37.000

2020-07-23 05:50:25.000

[Greg_Deckler · Accepted solution]

@askspepsi - OK, if I understand correctly use HOUR. If it is >= 12 it is PM otherwise AM

https://docs.microsoft.com/en-us/dax/hour-function-dax

[dax · Accepted solution]

Hi @askspepsi , 

You could try to refer to @Greg_Deckler 's measure, and change it  and apply this in table visual like below

Measure = IF(RIGHT(MAX('Table1'[date]) & "",2)=SELECTEDVALUE('Table'[time]), 1,0)

Best Regards,
Zoe Zhi

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