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.
Hello, I am trying to extract hours from my time column which contains hours in a hh:mm: SS format. I want to extract hours when the time is between 9:30 to 10:00 I want to get the value "9:30 - 10:00" otherwise I want to get the hours.
I used the following DAX formula, but it didn't work.
New column = IF([time]>=09:30:00; IF([time]<=10:00:00; "9:30 -10:00" ;HOUR([Time]))
I get the error, the syntax for ":" is incorrect
when I change the formula to
New Column = IF([Time]>=("09:30:00")&&[Time]<=("10:00:00");"9:30 -10:00";HOUR([Time]))
I get the error Dax comparision do not support comparing values of type date with text, but my time column has a datatype time.
Solved! Go to Solution.
Hi @Anonymous,
For your scenario, I create a data sample as example. Assuming that you have Time column like this.
Time |
8/2/2018 9:28:20 |
8/1/2018 9:30:00 |
8/1/2018 9:30:05 |
8/1/2018 9:45:20 |
8/1/2018 10:00:00 |
8/1/2018 10:01:00 |
8/2/2018 10:00:05 |
You could create a calculated column with the formula below.
Column = VAR h = HOUR ( 'Table'[Time] ) VAR m = MINUTE ( 'Table'[Time] ) VAR s = SECOND ( 'Table'[Time] ) RETURN IF ( h >= 9 && m >= 30, 'Table'[Time], IF ( h <= 10 && m = 0 && s = 0, 'Table'[Time] ) )
Then you will get the output like this:
Best Regards,
Cherry
Hi @Anonymous,
For your scenario, I create a data sample as example. Assuming that you have Time column like this.
Time |
8/2/2018 9:28:20 |
8/1/2018 9:30:00 |
8/1/2018 9:30:05 |
8/1/2018 9:45:20 |
8/1/2018 10:00:00 |
8/1/2018 10:01:00 |
8/2/2018 10:00:05 |
You could create a calculated column with the formula below.
Column = VAR h = HOUR ( 'Table'[Time] ) VAR m = MINUTE ( 'Table'[Time] ) VAR s = SECOND ( 'Table'[Time] ) RETURN IF ( h >= 9 && m >= 30, 'Table'[Time], IF ( h <= 10 && m = 0 && s = 0, 'Table'[Time] ) )
Then you will get the output like this:
Best Regards,
Cherry
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |