creating key for time table

[Anonymous]

hi,

as an intro, i'm trying to study the no. of runway movements [arrival (on block), departure (off-block), takeoffs (wheels-up) & touchdowns (wheels-down)] for a particular aiport. i have created a date-table & also a time-table as suggested in a youtube linked here. that video suggested that the timekeys be created & linked in the fact table & the time-table. 

however, as mentioned earler, i have 4 types of info that i need to measure i.e.

1a. planned arrival (on block) time
1b. actual arrival (on block) time

2a. planned departure (off-block) time
2b. actual departure (off-block) time

3. takeoff (wheels-up) time
4. touchdown (wheels-down) time

i tried to create a timekey that's pegged to 1 of those info (i.e. planned departure time). it worked in calculating the total no. of planned departures (using counta dax function).

it wouldn't, however, work to calculate for the totals of the other types of info (i.e. for items 1b, 2a, 2b, 3, & 4 listed above). i suspect that it's because the data model is based on linking 1 key in fact table (using planned departure time column) & another key in the time-table.

i'd appreciate urgent help to resolve this. 

the sample data can be found here. 

tks & krgds, -nik

[jdbuchanan71 · Accepted solution]

@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.

[jdbuchanan71 · Accepted solution]

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"
            )
    )

[Anonymous · Accepted solution]

@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

"HourlyQuartile",

SWITCH(

TRUE(),

[Minute] < 15, "0-14",

[Minute] < 30, "15-29",

[Minute] < 45, "30-44",

"45-59"

tks & krgds, -nik

[jdbuchanan71 · Accepted solution]

@Anonymous ,

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

[CTozzi]

@Anonymous what if you simply unpivot the columns? You will then have one row for each event and one single relationship.

[Anonymous]

hi @CTozzi,

the ori data table that m working on actually contains >26 mil rows of data. i don't think i want to take that path. the data sample (around 418 rows) i made for this was an extraction fm that ori data table.

tks fr the suggestion tho,

krgds: -nik

[CTozzi]

@Anonymous I understand. Anyway, that wouldn't be a problem for power BI. Give it a try if you want to simplify.

[jdbuchanan71 · Accepted solution]

@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.

[Anonymous]

many tks @jdbuchanan71.

can u kindly advise me on how u created the time table with the quartile info? i'd like to try that in my pbix too.

krgds, -nik

[jdbuchanan71 · Accepted solution]

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"
            )
    )

[Anonymous · Accepted solution]

@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

"HourlyQuartile",

SWITCH(

TRUE(),

[Minute] < 15, "0-14",

[Minute] < 30, "15-29",

[Minute] < 45, "30-44",

"45-59"

tks & krgds, -nik

[jdbuchanan71 · Accepted solution]

@Anonymous ,

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

[Anonymous · Accepted solution]

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

[Anonymous]

many tks again, @jdbuchanan71.

krgds, -nik