Measure help

[Anonymous]

Hi Power Users,

I'm new to power bi and trying to migrate formulas from tableau. 

IF DATEPART('hour',[Date of entry]) = 12 THEN '12 PM'

ELSEIF DATEPART('hour',[Date of entry]) = 13 THEN '1 PM'

ELSEIF DATEPART('hour',[Date of entry]) = 14 THEN '2 PM'

ELSEIF DATEPART('hour',[Date of entry]) = 15 THEN '3 PM'

ELSEIF DATEPART('hour',[Date of entry]) = 16 THEN '4 PM'

ELSEIF DATEPART('hour',[Date of entry]) = 17 THEN '5 PM'

ELSEIF DATEPART('hour',[Date of entry]) = 18 THEN '6 PM'

ELSEIF DATEPART('hour',[Date of entry]) = 19 THEN '7 PM'

ELSEIF DATEPART('hour',[Date of entry]) = 20 THEN '8 PM'

ELSEIF DATEPART('hour',[Date of entry]) = 21 THEN '9 PM'

ELSEIF DATEPART('hour',[Date of entry]) = 22 THEN '10 PM'

ELSEIF DATEPART('hour',[Date of entry]) = 23 THEN '11 PM'

ELSEIF DATEPART('hour',[Date of entry]) = 0 THEN '12 AM'

ELSE STR(DATEPART('hour',[Date of entry])) + ' AM'

END

How can i create above calculation in DAX. i need to use this column in Chart.

Hour column in x-axis

Please help

[mahoneypat]

FYI that there is no need for a nested IF or SWITCH, as you can do this in the query editor or with a DAC calculated column with this formula, where 'Hour'[Hour] should be replaced with your actual Table[Column] in a number format.

Column = FORMAT(TIME('Hour'[Hour],0,0), "h AM/PM")

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[lbendlin]

FORMAT([Date of Entry],"h AM/PM")

https://docs.microsoft.com/en-us/dax/custom-date-and-time-formats-for-the-format-function

[Tahreem24]

@Anonymous ,

You can the SWITCH :

Column = SWITCH(HOUR(Table[Date of Entry]),12,"12PM",1,"1PM",2,"2PM",3,"3PM",HOUR(Table[Date of Entry])&"AM")

So complete the list and at end in else part put HOUR(Table[Date of Entry])&"AM".

[az38]

Hi @Anonymous 

create a SWITCH column

Column = 
SWITCH(HOUR([Date of entry]),
12,'12 PM',
13, '1 PM',
....
0, '12 AM',
HOUR([Date of entry]) & ' AM'
)

[Anonymous]

---

@az38 wrote:

Hi @Anonymous 

create a SWITCH column

Column = 
SWITCH(HOUR([Date of entry]),
12,'12 PM',
13, '1 PM',
....
0, '12 AM',
HOUR([Date of entry]) & ' AM'
)

---

@az38 Thanks for your reply. This is working but when i select the date range from 01/01/2018 to 02/02/2020 the bar visual is showing multiple hours. Example 12 AM repeating many times based on the data. In X-Axis i have Hour and in Y-Axis i have count of ID's. I want to show 12AM,1AM,2AM... Count of ID for each hour. For example if i have 10 12AM's and count for each 10 then i have to show only 1 12AM and add all those counts.