Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
FORMAT([Date of Entry],"h AM/PM")
https://docs.microsoft.com/en-us/dax/custom-date-and-time-formats-for-the-format-function
@Kumarcy ,
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 wrote:Hi @Kumarcy
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |