0

I am trying to create a time table to make a filter range between 0 and 23 hs. This would be my first question but I can't even create any table because of the syntax error. (I don't remember how to do this). I am using Power BI Desktop and this is the DAX calculated table syntax:

DateTime = 
ADDCOLUMNS (
    CROSSJOIN(
        CALENDAR(DATE(2000;1;1);DATE(2025;12;31));
        UNION (
            ROW ( "Time"; TIME ( 1; 0; 0 ) );
            ROW ( "Time"; TIME ( 2; 0; 0 ) );
            ROW ( "Time"; TIME ( 3; 0; 0 ) );
            ROW ( "Time"; TIME ( 4; 0; 0 ) );
            ROW ( "Time"; TIME ( 5; 0; 0 ) );
            ROW ( "Time"; TIME ( 6; 0; 0 ) );
            ROW ( "Time"; TIME ( 7; 0; 0 ) );
            ROW ( "Time"; TIME ( 9; 0; 0 ) );
            ROW ( "Time"; TIME ( 10; 0; 0 ) );
            ROW ( "Time"; TIME ( 11; 0; 0 ) );
            ROW ( "Time"; TIME ( 12; 0; 0 ) );
            ROW ( "Time"; TIME ( 13; 0; 0 ) );
            ROW ( "Time"; TIME ( 14; 0; 0 ) );
            ROW ( "Time"; TIME ( 15; 0; 0 ) );
            ROW ( "Time"; TIME ( 16; 0; 0 ) );
            ROW ( "Time"; TIME ( 17; 0; 0 ) );
            ROW ( "Time"; TIME ( 18; 0; 0 ) );
            ROW ( "Time"; TIME ( 19; 0; 0 ) );
            ROW ( "Time"; TIME ( 20; 0; 0 ) );
            ROW ( "Time"; TIME ( 21; 0; 0 ) );
            ROW ( "Time"; TIME ( 22; 0; 0 ) );
            ROW ( "Time"; TIME ( 23; 0; 0 ) );
            ROW ( "Time"; TIME ( 24; 0; 0 ) )
        )
    );
    "DateTime"; [Date] + [Time];
    "Hour"; HOUR ( [Time] )
)

This gives me syntax error on ; (I've tried "," either). There is some easy stuff that I writing it wrongly I am pretty sure, where is the error / errors? Thanks

I tried with this code (pasted from Internet) and this don't work:

DATET= CALENDAR (DATE (2017, 1, 1), DATE (2017, 12, 31))
Improve this question
3
  • Your syntax works fine for me when I replace all ; with ,.
    – Alexis Olson
    Commented Aug 14, 2018 at 15:11
  • It doesn't work for me, with commas or semi-colons.
    – Maramal
    Commented Aug 14, 2018 at 15:12
  • Try creating a table using 'Enter Data' and then create a 'New quick measure' to see how Power BI creates the DAX formula syntax. I'm curious how a working measure looks.
    – Alexis Olson
    Commented Aug 14, 2018 at 16:43

2 Answers 2

Reset to default
1

The following works for me (and generates 227,928 rows):

DateTime = ADDCOLUMNS(
               CROSSJOIN(
                   CALENDAR(DATE(2000,1,1), DATE(2025,12,31)),
                   GENERATESERIES(0,23,1)
               ), 
               "DateTime", [Date] + TIME([Value], 0, 0)
           )
Improve this answer
6
  • This is not working, I tried with commas and semi-colons: i.imgur.com/rvnA0Ub.png
    – Maramal
    Commented Aug 14, 2018 at 15:11
  • It gives the same error when you use commas? It works fine in my program.
    – Alexis Olson
    Commented Aug 14, 2018 at 15:12
  • Do you have a different list separator defined in your regional settings?
    – Alexis Olson
    Commented Aug 14, 2018 at 16:20
  • Well, regional settings "Locale for import" is configured as "Spanish"... do you mean this?
    – Maramal
    Commented Aug 14, 2018 at 16:22
  • I mean, what character is shown for "List separator" like in the last image of the post I linked?
    – Alexis Olson
    Commented Aug 14, 2018 at 16:24
0

For some reason the number wrap was configured as default but still we had to use quotes to wrap the numbers like:

DateTime = 
ADDCOLUMNS (
    CROSSJOIN(
        CALENDAR(DATE("2000","1","1"),DATE("2025","12","31")),
        UNION (
            ROW ( "Time", TIME ( "1", "0", "0" ) ),
            ROW ( "Time", TIME ( "2", "0", "0" ) ),
            ROW ( "Time", TIME ( "3", "0", "0" ) ),
            ROW ( "Time", TIME ( "4", "0", "0" ) ),
            ROW ( "Time", TIME ( "5", "0", "0" ) ),
            ROW ( "Time", TIME ( "6", "0", "0" ) ),
            ROW ( "Time", TIME ( "7", "0", "0" ) ),
            ROW ( "Time", TIME ( "9", "0", "0" ) ),
            ROW ( "Time", TIME ( "10", "0", "0" ) ),
            ROW ( "Time", TIME ( "11", "0", "0" ) ),
            ROW ( "Time", TIME ( "12", "0", "0" ) ),
            ROW ( "Time", TIME ( "13", "0", "0" ) ),
            ROW ( "Time", TIME ( "14", "0", "0" ) ),
            ROW ( "Time", TIME ( "15", "0", "0" ) ),
            ROW ( "Time", TIME ( "16", "0", "0" ) ),
            ROW ( "Time", TIME ( "17", "0", "0" ) ),
            ROW ( "Time", TIME ( "18", "0", "0" ) ),
            ROW ( "Time", TIME ( "19", "0", "0" ) ),
            ROW ( "Time", TIME ( "20", "0", "0" ) ),
            ROW ( "Time", TIME ( "21", "0", "0" ) ),
            ROW ( "Time", TIME ( "22", "0", "0" ) ),
            ROW ( "Time", TIME ( "23", "0", "0" ) ),
            ROW ( "Time", TIME ( "24", "0", "0" ) )
        )
    ),
    "DateTime", [Date] + [Time],
    "Hour", HOUR ( [Time] )
)
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.