creating key for time table

Anonymous 

You can create a relationship between your time table and each of the time fields in your table.

5 of them will be inactive but we turn them on using meausre.

I made a basic measure that just counts the flights.

Flight Count = COUNTROWS ( Flights )

Then the first flights measure is just there so the name is the same format as the others.

Flights PLN_DEP_TIM = [Flight Count]

The active relationship betwen time and flights is on the PLN_DEP_TIM field so this one just works.

For the rest we do them like this.

Flights ACT_DEP_TIM = CALCULATE ( [Flight Count], USERELATIONSHIP ( Flights[ACT_DEP_TIM], time[Time] ) )

And you make one of these for each of the additional time columns, then we can make a visual like this.

The counts on the blank Quartile row are those records that did not have a time in that column.  I have attached my sample book for you to look at.

I just did that in excel then linked my .pbix to the .xls file to pull in the time table but you can generate it using DAX as a new table with this.

Times = 
VAR _Hour = SELECTCOLUMNS ( GENERATESERIES ( 0, 23 ), "Hour", [Value] )
VAR _Min = SELECTCOLUMNS ( GENERATESERIES ( 0, 59, 1 ), "Minute", [Value] )
VAR _Second = SELECTCOLUMNS ( GENERATESERIES ( 0, 0, 1 ), "Second", [Value] )
VAR _Times = ADDCOLUMNS ( CROSSJOIN ( _Hour, _Min, _Second ), "Time", TIME ( [Hour], [Minute], [Second] ) )
RETURN
    ADDCOLUMNS (
        _Times,
        "Quartile",
            SWITCH (
                TRUE (),
                [Hour] < 6, "12 AM - 6 AM",
                [Hour] < 12, "6 AM - 12 PM",
                [Hour] < 18, "12 PM - 6 PM",
                "6 PM - 12 AM"
            )
    )

jdbuchanan71 

hi again jdbuchanan71 ,

is it possible to add another column to the table using the same dax for hourly quartile using SWITCH as follows

Anonymous ,

Just add it as a calcualated column on your existing time table.

haha... that e-z !
many tks again, jdbuchanan71.
krgds, -nik