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,
I have log files that show US server time (-6) so I need to add 6 hours to the time column in my table.
I have a "Tijd VS" column that shows the date and time (e.g. 31-8-2018 0:00:00 --> A date/time column in Power BI)
I tried to add a new measure --> Tijd = 'EZproxy log'[Tijd VS]+ TIME(06;00;00)
But I got the error: A single value for column 'Tijd VS' in table 'EZproxy log' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Does it go wrong because I have a date/time column? I also tried the same formula with my date/time column separated in a date and a time column. In that case the +6 hours work but Power BI adds a date to it (e.g. 30-12-1899 6:00:00) which is also not correct.
What am I doing wrong? And how can I fix it?
Solved! Go to Solution.
Why not add the column in Query Editor instead of in DAX?
Add Custom Column:
[TimeColumn] + #duration(0,6,0,0)
Could you check if the below one is helpful
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-local-time-zone-in-DAX/td-p/43328
Hi,
Get this error when I try it as new measure.
So no, doens't work.
The syntax for '.1' is incorrect. (DAX(FORMAT('EZproxy log'[Tijd VS],"MM/dd/YYYY") & " " & REPLACE(FORMAT('EZproxy log'[Tijd VS],"HH:mm:ss").1.2,HOUR('EZproxy log'[Tijd VS])+6))).
It does work if you replace the , by ;
However it also introduces a new problem.
If I want to change anything in an existing column via edit queries I get an error back that there are more columns than expected.
Why not add the column in Query Editor instead of in DAX?
Add Custom Column:
[TimeColumn] + #duration(0,6,0,0)
Many thanks for the help!
Hi @Baskar,
I used Tijd = 'EZproxy log'[Tijd_VS] + TIME(6,0,0) as a new column and got this error: The syntax for '.0' is incorrect. (DAX('EZproxy log'[Tijd_VS] + TIME(6.0.0))).
Hi @Anonymous,
You could try this formula:
Measure = CALCULATE(SUM(Table1[Tijd VS]))+6/24
Result:
You could also download the pbix fiel to have a view.
Regards,
Daniel He
Hi,
Could you use it as a calculated column instead of measure,like below
Hi @vishnurk,
I added a calculated column with: Tijd = FORMAT('EZproxy log'[Tijd_VS];"MM/dd/YYYY") & " " & REPLACE(FORMAT('EZproxy log'[Tijd_VS];"HH:mm:ss"),1,2;HOUR('EZproxy log'[Tijd_VS])+6)
And got this error: The syntax for '.1' is incorrect. (DAX(FORMAT('EZproxy log'[Tijd_VS],"MM/dd/YYYY") & " " & REPLACE(FORMAT('EZproxy log'[Tijd_VS],"HH:mm:ss").1.2,HOUR('EZproxy log'[Tijd_VS])+6))).
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |